Forum Discussion

aileenkaz's avatar
aileenkaz
Frequent Visitor
4 years ago
Solved

Chart from multiple measures

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?

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    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's avatar
      aileenkaz
      Frequent Visitor
      4 years ago

      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):

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.