topic Re: Top 5 By Two different Conditions in DAX Commands and Tips

Top 5 By Two different Conditions

Anonymous — Sun, 03 May 2020 13:47:45 GMT

Hi Everyone,

I have a set of data given below. Iwant top 5 assoicate name by spend as well by division. For example the division (Top5 would be in Column) and associate name would be in Rows in Matrix Visualization.

Data:-

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

Expeted Output

Associate	Group	Home	Hospital	Services	Veg	Grand Total
A12					23434	23434
A5	4234					4234
A6		2344	434			3012
A4				555		555
A1		223				457

I am able to fetch top 5 associate name by spend but not by division (Need top 5 division as well as in column and overall total)

Kindly help.

Re: Top 5 By Two different Conditions

Greg_Deckler — Sun, 03 May 2020 14:34:47 GMT

This appears to be what you are going for?

Re: Top 5 By Two different Conditions

Mariusz — Sun, 03 May 2020 14:45:02 GMT

Hi Anonymous

Sure, you can use this Measure

Rank = RANKX ( ALL ( 'Table'[Associate] ), CALCULATE ( SUM ( 'Table'[Spend] ) ), , DESC, DENSE )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Top 5 By Two different Conditions

Anonymous — Sun, 03 May 2020 16:53:22 GMT

Hi Mariuz,

I can see top 5 associate name by spend but it is not fulfilling my requirments as I also need Top 5 Division it means in Rows I need associate name (Top 5 ) and in Column I need (Top 5 ) Division name and finally total.

Please help.

Re: Top 5 By Two different Conditions

Anonymous — Sun, 03 May 2020 17:20:47 GMT

Hi,

Thank you for your suggestion.

Need top 5 also Division in column and overall total in the end. In your visualisation I can see 6 division. And in my final data I have lots of other division.

Regards

UPhar

Re: Top 5 By Two different Conditions

mahoneypat — Sun, 03 May 2020 19:22:43 GMT

This one was tricky! (to do this inside a matrix visual). Here is one way to do it. The matrix matches your original visual (excep the total). I broke it up into pieces/variables to make it easier to follow/adjust. I ran out of time, but the you can it one step further to also get the total showing correct (i.e., sumx(values(SpendTable[Division]), [Top 5 Div and Assoc]), and use that as the measure instead). Not confirmed but that should work.

Top 5 Div and Assoc = var selecteddiv = SELECTEDVALUE(SpendTable[Division])

var selectedassoc = SELECTEDVALUE(SpendTable[Associate])

var summarytable = ADDCOLUMNS(ALL(SpendTable[Division], SpendTable[Associate]), "Spend", [SpendTotal], "AssocRank", CALCULATE([AssociateRank], all(SpendTable), SpendTable[Associate]=EARLIER(SpendTable[Associate])),"DivRank", CALCULATE([DivisionRank], ALL(SpendTable), SpendTable[Division]=EARLIER(SpendTable[Division])))

var filtered = Filter(summarytable, AND([AssocRank]<=5, [DivRank]<=5))

var divfiltered = Filter(filtered, SpendTable[Division]=selecteddiv)

var assocfiltered = FILTER(divfiltered, SpendTable[Associate]=selectedassoc)

var finalresult = SUMX(assocfiltered, [Spend])

return finalresult

If this works for you, please mark it as the solution. Kudos are great too. Please let me know if it doesn't or if any questions.
Regards,
Pat

Re: Top 5 By Two different Conditions

Anonymous — Sun, 03 May 2020 19:49:38 GMT

Hi Anonymous ,

This one is tricky and you can do it in the following way.

Your Master Table is named Top5

1. Create a Table for TOP 5 Associates.

TOP 5 Associates =
    TOPN (
        5,
        ADDCOLUMNS (
            SUMMARIZE ( Top5, Top5[Associate] ),
            "SUMASS",
            CALCULATE ( SUM ( Top5[Spend] ), ALLEXCEPT ( TOP5, Top5[Associate] ) )
        ),
       [SUMASS],
        DESC
    )

2. Create a Table for TOP 5 Divisions

Table 5 Divisions =
    TOPN (
        5,
        ADDCOLUMNS (
            SUMMARIZE ( Top5, Top5[Division] ),
            "SUMDIV",
            CALCULATE ( SUM ( Top5[Spend] ), ALLEXCEPT ( TOP5, Top5[Division] ) )
        ),
       [SUMDIV],
        DESC
    )

3. Create Calculated Column in TopN Table i.e Master Table

Top5 Associates Col = RELATED('TOP 5 Associates'[Associate])

4. Link the relationships

5. In Advance Filtering, Filter Top 5 Divisions and Top 5 Associates by isnotBlank.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Top 5 By Two different Conditions

Mariusz — Sun, 03 May 2020 21:54:22 GMT

Hi Anonymous

Try this Measure

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] )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Top 5 By Two different Conditions

Anonymous — Mon, 04 May 2020 04:12:30 GMT

Hi Mariusz,

Are these two seperate measures? I tried to create only one measure and not getting the desired Output i.e

See I am getting below output from your given DAX

Apologies, As I am noob in DAX queries.

Kindly Help.

Mariusz

Re: Top 5 By Two different Conditions

Mariusz — Mon, 04 May 2020 05:33:10 GMT

Hi Anonymous

Can you share your DAX expression?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Top 5 By Two different Conditions

Mariusz — Mon, 04 May 2020 06:25:35 GMT

Hi Anonymous

I've attached a file with a solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

Re: Top 5 By Two different Conditions

Anonymous — Mon, 04 May 2020 11:59:50 GMT

Hi Harsh,

I am using Direct Query and fetching data from SQl server hence I really dont want to add addtional column and table as my data size is huge.

Really appreciate for your suggesstion.

Regards

Uphar Tandon

Re: Top 5 By Two different Conditions

Anonymous — Mon, 18 May 2020 18:29:48 GMT

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

Re: Top 5 By Two different Conditions

Anonymous — Wed, 20 May 2020 18:31:19 GMT

Hi Mariusz

Thank you so much for your suggestion. Here, 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 performed.

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

Really appreciate your efforts.

Regards

Uphar Tandon