Forum Discussion

mdrammeh's avatar
mdrammeh
Icon for Helper III rankHelper III
8 years ago
Solved

How to Show Top 4 Highest Payment In Large Dataset

I am using PowerQuery to perform the following tasks and need some guidance on how to perform this task. I would also like to understand how I can use a similar criteria in Power BI deskstop.   The...
Need Help
Tutorial Requests
  • danextian's avatar
    danextian
    8 years ago

    Hi mdrammeh,

     

    Instead of doing it in M, why not just use DAX. You can  create a  measure using RANKX or TOPN. The measure would look something like this (I don't have PBI right now, so I'm only imagininig this, hopefully this wont return an error).

     

    RANK BY MONTH =
//calculate rank by month based on payment amount
VAR RANK_ =
    RANKX ( ALL ( 'Table'[Month Column] ), SUM ( 'Table'[Payment Amount] ) ) 
    //returns top 4 months only
RETURN
    CALCULATE ( SUM ( 'Table'[Payment Amount] ), FILTER ( 'Table', RANK_ <= 4 ) )

    Then you can place this measure inside a table or matrix together with the customer account number and the month. All non-top4 months should now show on the table.

     

     

danextian's avatar
danextian
Icon for Super User rankSuper User
8 years ago

Hi mdrammeh,

 

Instead of doing it in M, why not just use DAX. You can  create a  measure using RANKX or TOPN. The measure would look something like this (I don't have PBI right now, so I'm only imagininig this, hopefully this wont return an error).

 

RANK BY MONTH =
//calculate rank by month based on payment amount
VAR RANK_ =
    RANKX ( ALL ( 'Table'[Month Column] ), SUM ( 'Table'[Payment Amount] ) ) 
//returns top 4 months only
RETURN
    CALCULATE ( SUM ( 'Table'[Payment Amount] ), FILTER ( 'Table', RANK_ <= 4 ) )

Then you can place this measure inside a table or matrix together with the customer account number and the month. All non-top4 months should now show on the table.