Forum Discussion

powerbiuser9999's avatar
powerbiuser9999
Helper I
4 years ago
Solved

TOPN function in CALCULATE

Hi I have an interesting problem when trying to create a measure in PBI Desktop. I have data thats akin to this, which I have used SUMMARIZE to create categories and a count of their respective...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, powerbiuser9999 

     

    You can try the following methods.

    1. Create a new calculation column to rank the COUNT of sales.
    Rank = 
RANK.EQ ( [COUNT of sales], 'Table'[COUNT of sales], DESC )

     

        2. Create a measure to sum the top five.

    Measure =
CALCULATE (
    SUM ( 'Table'[COUNT of sales] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Rank] <= 5 )
)

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more.

v-zhangti's avatar
v-zhangti
Community Support
4 years ago

Hi, powerbiuser9999 

 

You can try the following methods.

  1. Create a new calculation column to rank the COUNT of sales.
Rank = 
RANK.EQ ( [COUNT of sales], 'Table'[COUNT of sales], DESC )

 

    2. Create a measure to sum the top five.

Measure =
CALCULATE (
    SUM ( 'Table'[COUNT of sales] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Rank] <= 5 )
)

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more.