Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Gus_C
Frequent Visitor

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

 

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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 ( selTopNDISTINCT ( Table[Clients] ), [your measure], DESC )
    )

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

7 REPLIES 7
mahoneypat
Microsoft Employee
Microsoft Employee

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
Microsoft Employee
Microsoft Employee

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 ( selTopNDISTINCT ( Table[Clients] ), [your measure], DESC )
    )

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Gus_C
Frequent Visitor

Hi @DataVitalizer , thanks for your reply

 

Gus_C_0-1627301583273.png

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

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

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
Super User
Super User

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

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors