Combo line/clustered bar chart with static line and dynamic columns using a slicer

[Ashfadir]

Hello!

I am trying to create a combo line/clustered bar chart where the line remains static and the bars are dynamic data based on selections in a slicer. The line is intended to show the sum of all categories in the slicer (regardless of whether they are selected or not). With my current attempt, when a category is chosen in the slicer, the line in the chart becomes the sum total of the selected categories (second picture).

Here is a link to the pbix file I am working with:

https://1drv.ms/u/s!AurH6DkRYYnJiTcACNdPU6M5XttE?e=u0SC34

Thanks in advance to anyone who can help!

[Ashish_Mathur · Accepted solution]

Hi,

Drag these measures to the visual

V = SUM(Table1[Volume])

V of all technologies = CALCULATE([V],ALL(Table1[Technology]))

Hope this helps.

[Ashfadir]

Thank you Ashish!

I am a complete novice when it comes to PowerBI so I want to leave some more explicit notes on how you need to deploy this solution to do what I requested:

1) To create the measures you need to click on the visual, and then click "New measure" under the Modeling section at the top.

2) The first measure "V = SUM(Table1[Volume])" does not need to be dragged to the visual. It is (I am guessing) just to define the variable "V"

3) The second measure "V of all technologies = CALCULATE([V],ALL(Table1[Technology]))" be dragged into the "Line y-axis" section of the Visualizations tab on the right. Remove anything else there if you only want that to be the line.

Extra note: By default, my visual would convert to a double Y chart if I had certain combinations selected in the slicer. When I went to the "Secondary y-axis section in the "Format visual" section, the Values were set to off. To stop it from converting to double Y, I simply had to toggle the values on and then off again.

Thanks again for the assistance!

[Ashish_Mathur]

You are welcome.  Yes to all the 3 statements.