Bar Chart - side-by-side comparison of category average and individual item?

user007486
user007486 ⚪️
in Card Building

Hello All,

I'm interested in building the following type of graphic.

Let's say I have a category such as 'Truck' and want to calculate the average cost for all categories 'Truck'.

And let's say I would like to have a side-by-side comparison of that average compared to a particular 'Truck'.

I'm having a tough time achieving this without hardcoding values.

Any advice would be appreciated.

Comments

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    You can do this easily in Magic ETL. It would look something like this.

    count.PNG

    Perform a group by of all trucks to get the totals.

    Join it back to your main dataset. You will then have that All Trucks total next to your individual truck total as another column.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    Instead of JOIN'ing the data as @Mark proposes, I would APPEND the data to itself. Then replace the "TruckID" value with "Truck_Total"


    this way your dataset will still respond to filters and you don't have to do weird math.


    sum(case when truck_id <> 'truck_total' then amount end) / sum(case when truck_id = 'truck_total' then amount end)
  • user007486
    user007486 ⚪️

    Great thanks for the responses guys. I'll give it a shot!

Categories