Chart from multiple measures

[aileenkaz]

Hi,

I have a data table with six columns:

Q4_1, Q4_2, Q4_3, Q4_4, Q4_5, Q4_6

each of them containing values 1-4.

Client wants to see the Top2box (3&4) values for the whole Q4 question in one graph. I have calculated the Top2Box values for all of them and saved them in measures. They should be shown in one chart as in example below.

The problematic part is that I cannot save those measures into a new table (which would be easily shown in the chart), because the values are changing when data table is filtered.

Is there any way how to put six distinct measures into a chart like this?

[Anonymous · Accepted solution]

Hi @aileenkaz ,

I have built a data sample:

And to avoid making changes to the dataset, I'd create a calculated table for Axis:

For axis = {"Q4_1", "Q4_2", "Q4_3", "Q4_4", "Q4_5", "Q4_6"}

Then create the measure:

Q4_x_top2box = 
var _count1=CALCULATE(COUNTROWS('DATA'),FILTER('DATA',[Q4_1] in {3,4}))
var _count2=CALCULATE(COUNTROWS('DATA'),FILTER('DATA',[Q4_2] in {3,4}))
var _count3=CALCULATE(COUNTROWS('DATA'),FILTER('DATA',[Q4_3] in {3,4}))
var _count4=CALCULATE(COUNTROWS('DATA'),FILTER('DATA',[Q4_4] in {3,4}))
var _count5=CALCULATE(COUNTROWS('DATA'),FILTER('DATA',[Q4_5] in {3,4}))
var _count6=CALCULATE(COUNTROWS('DATA'),FILTER('DATA',[Q4_6] in {3,4}))
var _switch=SWITCH(MAX('For axis'[Value]),"Q4_1",_count1,"Q4_2",_count2,"Q4_3",_count3,"Q4_4",_count4,"Q4_5",_count5,"Q4_6",_count6)
return 
 DIVIDE(_switch,COUNTROWS('DATA')+0)

Below is the output:

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

[PhilipTreacy]

Hi @aileenkaz 

I'm confused.  You say you have 6 columns containing values 1 to 4.  But your chart shows 5 values and multiple categories.  How does that relate to your data?  What does your actual data look like, can you share please.

Regards

Phil

[aileenkaz]

Hi, sorry, the chart was only an example sent by client.

I cannot send you data because of confidentiality, but what I have is:

• 6 columns containing values 1 to 4
• a measure (with value 0 to 100) calculated for each column x with this formula:

Q4_x_top2box = DIVIDE((COUNTROWS(FILTER(ALLSELECTED('DATA'),[Q4_x] = 3))+(COUNTROWS(FILTER(ALLSELECTED('DATA'),[Q4_x] = 4)))),COUNTROWS('DATA'),0)

What I want to do is to create this kind of chart, where the blue line will be consisting of the measures (Top2box values):