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 will have the value of sales for top 5, 10, 20, and 50 clients and will be used with a date slicer

 

Is this possible to do? and how would I go about it? Ive yet to come across a solution for this kind of graph in PowerBI.

 

Many thanks!

-Gus

 

 

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

     

7 Replies

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

    Hi Gus_C 

    Could you please provide us with a drawing of what you are trying to acheive ? That would help us understand the request 👍.

     

  • 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

  • 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