Forum Discussion

Gus_C's avatar
Gus_C
Frequent Visitor
5 years ago
Solved

Multiple Top N in the x-axis

Hi all, been struggling with creating a particular graph this morning:   I am looking to create a bar graph with 4 bars: Top 5, top 10, top 20, and top 50 clients by value of sales. Essentially wi...
Need Help
Show and Tell
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    You can create a single disconnected table like this one called 'TopNTable'.

     

    Label

    TopNValue
    Top 5 5
    Top 10 10
    Top 25 25

     

    You can then use the Label column in your visual, and use MIN(TopNTable[TopNValue]) in a variable, and then use that variable in your TOPN measure expression

     

    Dynamic TopN Measure =
    VAR selTopN =
        MIN ( TopNTable[TopNValue] )
    RETURN
        CALCULATE (
            [your measure],
            TOPN ( selTopN, DISTINCT ( Table[Clients] ), [your measure], DESC )
        )

     

    Pat

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

You can create a disconnected table with those values (5, 10, 50, etc.) and the desired label (e.g., "Top 5", "Top 10"), and then use that column on the axis in your visual.  You can then grab that value as a variable in your measure (e.g., MIN(TopNTable[TopNValue]) ) to dynamically adjust the TOPN.

 

Pat

 

Gus_C's avatar
Gus_C
Frequent Visitor
5 years ago

Thanks for the reply, mahoneypat 

Would I need to create a disconnected table for each TopN? so 4 disconnected tables with sum of sales for top 5, top 10 clients etc?

 

Thanks,

Gus

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    You can create a single disconnected table like this one called 'TopNTable'.

     

    Label

    TopNValue
    Top 5 5
    Top 10 10
    Top 25 25

     

    You can then use the Label column in your visual, and use MIN(TopNTable[TopNValue]) in a variable, and then use that variable in your TOPN measure expression

     

    Dynamic TopN Measure =
    VAR selTopN =
        MIN ( TopNTable[TopNValue] )
    RETURN
        CALCULATE (
            [your measure],
            TOPN ( selTopN, DISTINCT ( Table[Clients] ), [your measure], DESC )
        )

     

    Pat