Top 5 in Rows and Columns

[Anonymous]

Hi Everyone,

Adding one more situation in the given scenario. (I am using SQL server direct query source hence unable to add new tables in model)

I want to filter category first and then in the filter category I want fetch top 5 associate name and top 5 divison in that particulat category.

Below is the Raw data

Category	Associate	Division	Spend
Business	A1	Road	234
Asset	A2	Services	343
Electriical	A1	Home	223
Labour	A4	Services	555
Business	A5	Group	4234
Asset	A6	Medical	234
Electriical	A7	Music	344
Labour	A6	Home	2344
Business	A9	Vehicle	434
Asset	A6	Hospital	434
Electriical	A11	Class	2
Labour	A12	Veg	23434
Business	A13	Cream	234
Asset	A6	Home	2344
Electriical	A9	Vehicle	434
Labour	A6	Hospital	434
Asset	A11	Class	2

Result would be say I filtered Category using page level Filter as Asset. Therfore under asser Top5 supplier and Top 5 spend would be like below snapshot. Mine motive is to segereate the Associate name by category and under cateory which are the Top5 associate and their spend with Top5 division(In column in powerBi)

Kindly help I am struggling on this from last couple of weeks. I am new in PowerBi therefore need support from you.

Regards

Uphar Tandon

[Greg_Deckler]

Well, seems like RANKX and TOPN are out-of-the-question: https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc...

So maybe this will help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

[Anonymous]

Hi @Greg_Deckler 

Already gone throught  but still unable to create the DAX query or you can say the DAX is not given the correct output.

I am using below DAX but when I am applying Page level Filter (Say in Category as Asset) the result it not accurate.

Please find the DAX 

SpendTop5= 
VAR __topN = 5
VAR __associate = 
    RANKX (
        ALL ( 'Table'[Associate] ),
        CALCULATE( 
            [spend], 
            ALLEXCEPT( 'Table', 'Table'[Associate] ) ),
        ,
        DESC,
        DENSE
    ) <= __topN
VAR __division = 
    RANKX (
        ALL ( 'Table'[Division] ),
        CALCULATE( 
            [spend], 
            ALLEXCEPT( 'Table', 'Table'[Division] ) ),
        ,
        DESC,
        DENSE
    ) <= __topN
RETURN 
    IF( __division && __associate, [spend] )

If you could see, I am applying category filter in my dashboard therefore result is not accurate. I am looking for a DAX that can filter Category and then fetch top5 associate name and their Top5 Division.

Thanks

Uphar

[Greg_Deckler · Accepted solution]

@Anonymous - Try replacing your ALL statements with ALLSELECTED. ALLSELECTED is kind of tricky but it might work for you. But, the issue is almost certainly your ALL statements.

[Anonymous]

Hi @Greg_Deckler 

Thank you so much for your suggestion. I replaced All with All selected and now able to get the results.

The only change I have done here is that I am only fetching divsion from DAX Top 5 associate names are filtering out through powerbi Top N Filter option and for Category I am filtering through page level filter. 

But its works and giving the correct output.

Below Is the query which I perfromed.

VAR __division = 
    RANKX (
        SelectedALL ( 'Table'[Division] ),
        CALCULATE( 
            [spend], 
            ALLEXCEPT( 'Table', 'Table'[Division] ) ),
        ,
        DESC,
        DENSE
    ) <= __topN
RETURN 
    IF( __division && __associate, [spend] )

Really appreciate

Regards

Uphar Tandon