Help with Top N

[esims123]

I want to have the top 5 sites appearing within my dataset by each month.

Currently I can only get the top 5 appearing overall rather than depending on month. 

The collumn "Created" is the date the site appeared however when I try to incoperate this it doesn't appear how I need it to be.

[Anonymous]

Hi @esims123 ,

Please try create a new measure:

SiteTop5 = 
VAR _table = 
GENERATE(
    ALLSELECTED('Calendar'[Month]),
    TOPN(
        5,
        ALLSELECTED('Heater Callout Data'[Site]),
        [CountofSite]
    )
)
VAR _result = CALCULATE([CountofSite],KEEPFILTERS(_table))
RETURN
_result

And apply it to the y-axis.

Ranking – DAX Patterns

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[esims123]

hey, couldnt get this to work, thanks

[Anonymous · Accepted solution]

Hi @esims123 ,

Please try this:

SiteTop5 =
VAR _table =
    GENERATE (
        ALLSELECTED ( 'Heater Callout Data'[Created].[Month] ),
        TOPN (
            5,
            ALLSELECTED ( 'Heater Callout Data'[Site] ),
            COUNT ( 'Heater Callout Data'[Site] )
        )
    )
VAR _result =
    CALCULATE ( COUNT ( 'Heater Callout Data'[Site] ), KEEPFILTERS ( _table ) )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[esims123]

that's great thanks, do you know if theres a way to select the top 5 without there being multiples who are within the top 5. For example there are several sites that have the same number of callouts which mean there are several sites within the top 5 rather than just 5.

thanks

[amitchandak]

@esims123 , use a measure created with TOPN or window

CALCULATE([meausre],TOPN(5,all(Table[site]),[Measure],DESC),VALUES(Table[site]))

Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ