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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

top value filters in donut chart

Dataset.JPG

 

Hi,

 

Requirement:  Two donut charts – One to show top city and second chart to show top second city.

 

When user select “India” from Country filter, First donut should show department of Coimbatore, since the count of Coimbatore is highest. Second chart must show department of Chennai.

 

We have tried with Top N filters, It doesn’t have an option to choose second top.

 

Any leads would be appreciated.

Thanks in Advance.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @Anonymous,

You can consider using measure formula to check the top n category and use it on visual level filter to filter the second one.

Measure =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( table ),
        [Country],
        [City],
        "Count", COUNT ( table[Department] )
    )
VAR _top =
    MAXX ( summary, [Count] )
VAR _second =
    MAXX ( FILTER ( summary, [Count] < _top ), [Count] )
VAR _city =
    MAXX ( FILTER ( summary, [Count] = _second ), [City] )
RETURN
    IF ( SELECTEDVALUE ( table[City] ) = _city, "Y", "N" )

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @Anonymous,

You can consider using measure formula to check the top n category and use it on visual level filter to filter the second one.

Measure =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( table ),
        [Country],
        [City],
        "Count", COUNT ( table[Department] )
    )
VAR _top =
    MAXX ( summary, [Count] )
VAR _second =
    MAXX ( FILTER ( summary, [Count] < _top ), [Count] )
VAR _city =
    MAXX ( FILTER ( summary, [Count] = _second ), [City] )
RETURN
    IF ( SELECTEDVALUE ( table[City] ) = _city, "Y", "N" )

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@Anonymous , Rank should help you.

Refer if this blog can help - One visual have only top1 another have only 2nd topper

https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814

 

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
harshnathani
Community Champion
Community Champion

@Anonymous ,

 

I think you need something like this.

 

123.JPG

 

Create a table TOPN with value 1 to 10 to use as filter.

 

Rank the Column

rank = RANKX(DATA,DATA[SALES],,DESC)
 
and then do a top X slider
 
_Top X =
CALCULATE(SUM(DATA[SALES]),FILTER(DATA,DATA[rank]>=MIN('TOPN'[TOP N])&&DATA[rank]<=MAX('TOPN'[TOP N])))
 
Let me  know if this works.
 
Regards,
Harsh Nathani
 

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors
Top Kudoed Authors