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:-AssociateDivisionSpendA1Road234A2Services343A1Home223A4Services555A5Group4234A6Medical234A7Music344A6Home2344A9Vehicle434A6Hospital434A11Class2A12Veg23434A13Cream234 Expeted OutputAssociateGroupHomeHospitalServicesVegGrand TotalA12 2343423434A54234 4234A6 2344434 3012A4 555 555A1 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.

Top 5 By Two different Conditions | Microsoft Fabric Community

13 Replies

Mariusz

Community Champion

6 years ago

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

Anonymous
Not applicable
6 years ago
Hi Mariusz,

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

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

See I am getting below output from your given DAX

Apologies, As I am noob in DAX queries.

Kindly Help.

Mariusz
- Mariusz
  Community Champion
  6 years ago
  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

Greg_Deckler
Community Champion
6 years ago
This appears to be what you are going for?
- Anonymous
  Not applicable
  6 years ago
  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
  - mahoneypat
    Microsoft Employee
    6 years ago
    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
Mariusz
Community Champion
6 years ago
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
- Anonymous
  Not applicable
  6 years ago
  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.
Anonymous
Not applicable
6 years ago
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])
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!!
- Anonymous
  Not applicable
  6 years ago
  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

Anonymous

Not applicable

6 years ago

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

Forum Discussion

Top 5 By Two different Conditions

13 Replies

Related content

Conditional formatting for difference between bars?

TOP N

Top 5 in descending order

Different schema into different destination

TOP N