Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 M...
Top N
  • Mariusz's avatar
    Mariusz
    6 years ago

    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's avatar
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's avatar
    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