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

Hi DataVitalizer , thanks for your reply

 

This graph in excel is sort of what im looking to get in powerBI. The sum of sales for 4 different topN's, by client. Hope this helps in explaining the problem

 

Many thanks,

-Gus

DataVitalizer's avatar
DataVitalizer
Icon for Super User rankSuper User
5 years ago

Hi Gus_C 

 

I suggest the following steps to end up with that graph in Power BI

 

1. Create a calculated column in your fact table using this formula to rank the rows by numeric values

RankingColumn= RANKX(Your_Table,Numeric_Column,,DESC,Dense)

 

2. For each bar you will need to create a measure as below:

Top3 = CALCULATE(SUM(Numeric_Column),RankingColumn<=3)
Top10 = CALCULATE(SUM(Numeric_Column),RankingColumn<=10)

 3. Drag these measures into your grouped histogram ðŸ‘Œ

 

Did it work ? Mark it as a solution to help spreading knowledge.

A kudos would be appreciated

  • Gus_C's avatar
    Gus_C
    Frequent Visitor
    5 years ago

    Thanks a lot DataVitalizer ,

     

    so far works great, only issue is ranking seems to be static. 

     

    The columns in my table are: Date, client, sales

     

    How would I change ranking column to be dynamic with date slicers?

     

    Many thanks,

    Gus