Multiple Top N in the x-axis

[Gus_C]

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

[mahoneypat]

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]

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 · Accepted solution]

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]

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]

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]

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

[DataVitalizer]

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