How to Show Top 4 Highest Payment In Large Dataset

[mdrammeh]

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

I have a large dataset with a list of customer accounts showing 12 consecutive payments by each customer. For each customer, I only want to capture the top  4 highest payments received over the last 12 months. This could either be a calculated column or summary. 

What would be the best approach to tackle this problem?

Thank you in advance. 

[danextian · Accepted solution]

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.