Adding totals to a clustered column chart

[Ali_Chibani]

Hello everyone,

I created a dummy spreadsheet and PBI files to help me explain the issue I am facing since I am working on confidential data.

I am trying to create a clustered column chart in which I display car sales by type as you can see in the image below.

As you can see, there are three car types. I want to add a fourth column that displays total sales (i.e., the sum of sales of all three types). I thought of adding new rows to my table that calculate total sales per month; however, I am tempted not to alter my table.

I would highly appreciate your support on this.

Sincerely,

Ali 

[kpost · Accepted solution]

See attached .pbix file containing a working solution.

I created a new table like this:

Sales_Total = UNION(SELECTCOLUMNS(Sales, "Date", [Date], "Car Type", [Car Type], "Sales", [sales])

                    , SELECTCOLUMNS(Sales, "Date", [Date], "Car Type", "Total", "Sales", [sales]))

 

Note that in the first one, I pull in [Car Type] for the "Car Type" column, while in the second one, the "Car Type" column is ALWAYS the value "Total".  I then use the [sales] from that table in the visual and [Car type] from that table in the legend.

See below.

 

 ///Mediocre Power BI advice, but it's free///

[kpost · Accepted solution]

See attached .pbix file containing a working solution.

I created a new table like this:

Sales_Total = UNION(SELECTCOLUMNS(Sales, "Date", [Date], "Car Type", [Car Type], "Sales", [sales])

                    , SELECTCOLUMNS(Sales, "Date", [Date], "Car Type", "Total", "Sales", [sales]))

 

Note that in the first one, I pull in [Car Type] for the "Car Type" column, while in the second one, the "Car Type" column is ALWAYS the value "Total".  I then use the [sales] from that table in the visual and [Car type] from that table in the legend.

See below.

 

 ///Mediocre Power BI advice, but it's free///

[Ali_Chibani]

Hey @kpost,

Thank you very much for your prompt response and for sharing the PBI file.

It works well now!

Sincerely,

Ali

[TomMartens]

Hey @Ali_Chibani,

this is simply not possible using the default charts,, and to be honest, I think it will have a great impact on the readibility of a column chart due to the size of the totak values.

From my knowledge a Total can be visualized by the Zebra BI visual and of course you create this as well using the custom visual Deneb (using an aggregate transform). I'm sure there are more custom visuals that provide this kind of "feature".

Hopefully, this provides you some ideas of how to tackle your challenge.

Regards,

Tom

[Ali_Chibani]

Hey @TomMartens,

Thank you very much for your prompt response and for your vital suggestions!

I am relatively new to Power BI, so would you kindly be able to share with me some resources through which I can learn more about Zebra BI visuals and the custom visual Deneb?

Sincerely,

Ali 

[TomMartens · Accepted solution]

Hey @Ali_Chibani ,

here you will find information about the Zebra BI visuals: Zebra BI | Actionable Reporting Made Easy

This site explains Deneb: Declarative Visualization in Power BI | Deneb (deneb-viz.github.io)

Here you will find everything related to vega-lite, the grammar o fgraphics that fuels Deneb: Introduction to Vega-Lite | Vega-Lite

Regards,

Tom

[Ali_Chibani]

Hello @TomMartens,

Thank you very much, I appreciate it.

Sincerely,

Ali