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 grap...
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.

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