Dynamic Clustered Column Chart

[Vetinari]

Dear Community,

in my project, I try to compare the cost structure of a product portfolio. There are 30 products in my portfolio (Prod. 1,..., 30), each of them belongs to a certain product category (fruit, vegetables, drinks, others). The production of each product involves four different types of cost (Cost A, B, C, D).

In the end, my aim is to generate a clustered column chart, with the four cost types on the X axis and a comparison of product cost, cost of category and total costs in each column-cluster:

The product is selected with a slicer:

My simple data model looks like this:

Cost per product, per category and for all products are defined as measures:

Cost_A = SUMX(COST,COST[Cost A])

Cost_A_Category = CALCULATE([COST_A], ALLEXCEPT(Categories,Categories[Product Category]))

Cost_A_All = CALCULATE([COST_A], ALL(Categories[Product Category],Categories[Product ID]))

Now I have 12 different measures. I can put all twelve next to each other in a graph, but I need them to be clustered by cost type. How can I do this?

One idea I had, was to generate a table/matrix with cost type in the first column and the names of the different measures in the columns for product/category/all comparison. But how do I get the measures working in the visual?

Anyl help of idea is highly appreciated!!

Thanks!

[V-lianl-msft]

Hi @Vetinari ,

You can try to create a disconnected table as a slicer, and then created a switch measure to show the different measures by slicer.

You can also use the Tabular Editor to achieve this.

Please refer to this blog:

https://community.powerbi.com/t5/Community-Blog/Using-a-slicer-to-show-different-measures/ba-p/17245... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vetinari]

Thanks! But that would still mean that I can show only one measure in the clustered column chart at a time, right?

[lbendlin]

No, you can have multiple measures.

[lbendlin · Accepted solution]

Have you considered using Calculation Groups for that?  They come with a cost (no pun intended) - You would have to learn how to use Tabular Editor.  On the other hand they seem to fit your need perfectly.

[Vetinari]

That was a great hint! Thank you! Calculation groups significantly reduce the number of measures. But still I can only compare one type of cost in one clustered column chart. PowerBI won't allow me to add another measure into the values field.:

Is there some kind of work-around?

[lbendlin]

You can either have a legend or multiple values, not both. There may be custom visuals that support that.