Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Top 5 By Two different Conditions

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:-

AssociateDivisionSpend
A1Road234
A2Services343
A1Home223
A4Services555
A5Group4234
A6Medical234
A7Music344
A6Home2344
A9Vehicle434
A6Hospital434
A11Class2
A12Veg23434
A13Cream234

 

 

Expeted Output

AssociateGroupHomeHospitalServicesVegGrand Total
A12    2343423434
A54234    4234
A6 2344434  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.

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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

 

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

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

 

CategoryAssociateDivisionSpend
BusinessA1Road234
AssetA2Services343
ElectriicalA1Home223
LabourA4Services555
BusinessA5Group4234
AssetA6Medical234
ElectriicalA7Music344
LabourA6Home2344
BusinessA9Vehicle434
AssetA6Hospital434
ElectriicalA11Class2
LabourA12Veg23434
BusinessA13Cream234
AssetA6Home2344
ElectriicalA9Vehicle434
LabourA6Hospital434

Asset

A11Class2

 

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)

 

Capture.PNG

 

 

 

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

 

Mariusz
Community Champion
Community Champion

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] )
image.png
 
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

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

 

Capture.PNG

 

Apologies, As I am noob in DAX queries.

 

Kindly Help.

 

@Mariusz 

 
 

 

 

 

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

 

Mariusz
Community Champion
Community Champion

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

 

Anonymous
Not applicable

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

harshnathani
Community Champion
Community Champion

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])
 
123.JPG
 
4. Link the relationships
 
1234.JPG
 
 
5.  In Advance Filtering, Filter Top 5 Divisions and Top 5 Associates by isnotBlank.
 
124.JPG
 
 
1.JPG
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
Anonymous
Not applicable

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

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

Sure, you can use this Measure 

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

image.png

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

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.

Greg_Deckler
Super User
Super User

This appears to be what you are going for?

 

image.png


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors