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

     

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