top value filters in donut chart

[Anonymous]

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.

[Anonymous · Accepted solution]

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

[amitchandak]

@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

[harshnathani]

@Anonymous ,

I think you need something like this.

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