Measure of Total as Column in Chart

[tami]

Hello all,

I have already searched some forum posts, but most of the time the new total bar is the sum of an existing variable, in my case it would be a measure for which I would also need the total in the chart. Have also tried codes I found in the community forum, however they don't seem to work with Measures instead of variables or I just applied them wrong. But more specific:

Here is a small example dataset with the necessary variables q1.1 and the locations. The measure "q1.1 Measure T2B" calculates the percentage of the expressions 4 and 5. This calculation also works in Power BI and looks like this:

q1.1 Measure T2B =
(
( CALCULATE ( COUNT ( surveydata[q1.1] ), surveydata[q1.1] = 4 ) )
+ ( CALCULATE ( COUNT ( surveydata[q1.1] ), surveydata[q1.1] = 5 ) )
)
/ ( CALCULATE ( COUNT ( surveydata[q1.1] ), ALL ( surveydata[q1.1] ) ) )

Nr.	q1.1	Locations
1	4	2 - Standort A
2	4	2 - Standort A
3	2	2 - Standort A
4	3	2 - Standort A
5	4	2 - Standort A
6	5	2 - Standort A
7	5	2 - Standort A
8	4	2 - Standort A
9	5	2 - Standort A
10	5	2 - Standort A
11	4	2 - Standort A
12	3	2 - Standort A
13	5	2 - Standort A
14	1	3 - Standort B
15	2	3 - Standort B
16	3	3 - Standort B
17	1	3 - Standort B
18		3 - Standort B
19	4	3 - Standort B
20	3	3 - Standort B
21	4	4 - Standort C
22	3	4 - Standort C
23	4	4 - Standort C
24	4	4 - Standort C
25	4	4 - Standort C
26	4	4 - Standort C
27	3	4 - Standort C
28	3	4 - Standort C

I would need in this chart the breakdown by locations and the total of it. However, the percentages should not be added up here, but should also be calculated for the total. See screenshot below, where I have done this in a bar chart for total and a bar chart for the locations only in one.

If there is already a solution and maybe I just couldn't apply it or didn't find the right thing, I'm sorry. I'm still quite new to the Power BI Game and still have a hard time with all the measures 😕

Thank you all in advance for your help! I really appreciate it

[Anonymous · Accepted solution]

Hi @tami,

You can refer to the following blog to know how to use filters to check the current row context level, then you can use if statements with these filters and expressions replace the original results.

Clever Hierarchy Handling in DAX - SQLBI

Regards,

Xiaoxin Sheng

[davehus]

Hi @tami ,

To be clear, do you want the total of the table or the total of each stadort to get your % ratio?

[tami]

Hi davehus,

thanks for your reply! I want the Total of the table (the 59%) in the chart by each Standort. So when the Measure is calculated for the whole table the result is the 59% and this should be also be a column in the chart with the 3 columns by standort. So that the current 2 charts as shown above are only one chart. Could that answer your question? 🙂

[davehus]

Hi @tami,

You could do something along the lines of individual measures for each survey and then the total>create a disconected table and used SWITCH to display the data but this would be dependant on how many surveys you have. 

There might be a visual either. I'm not 100% sure on this one.

Did I help you today? Please accept my solution and hit the Kudos button.