Issue with DAX calculation (RANK) on a specific aggregation level

[DZet]

Hi guys!
I guess my problem was solved on the Internet already at least "a few" times, but I hope you'll be understanding. Here is the thing:

We have:

-Sales (fact table) with OrderNo, OrderedItemKey, OrderDt, CustomerKey, and Amount.

More than one row (OrderedItem) may be in the same OrderNo.
There is a slicer for dates in the report, a slider of the between type, to set the max/min of the date range.

The task is to display the TOP 5 customers sorted by the highest order amount (not total ordered amount) in the selected year up until the selected date.

I'd love to see the proper and "elegant" solution.
I've created a precalculated table to aggregate facts for the Order level and tried to use the RANKX function, but I'm not able to apply the date filter properly, to get a kind of YTD range (max date from the slicer and min as Jan 1st of the same year).
I'd be grateful for any help! 🙂

[Anonymous]

Hi @DZet ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create  a measure. 

Measure = var _sellrank = SELECTEDVALUE('Sales'[SellRank])
var _t =DISTINCT(SELECTCOLUMNS( FILTER(ALLSELECTED( 'Sales') , 'Sales'[SellRank] <= _sellrank) ,"rank" , [SellRank]))
return
COUNTROWS(_t)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[DZet]

Hi!

Unfortunately, it's still not correct. As I described, this rank should be kind of YTD, so limited not straight by the slicer, but by such date range:

var MaxDt = MAX(DateDim[Date])
var MinDt = DATE(YEAR(MaxDt), 1, 1)

The slicer limits MaxDt, but we create ranking starting from Jan 1st. And that's the hardest part, I don't know how to change this filter context.

[Anonymous]

Hi @DZet ,

You can try to add multiple fields to a hierarchy slicer.

Please refer to the following document for more information:

Slicers in Power BI - Power BI | Microsoft Learn

Add multiple fields to a hierarchy slicer - Power BI | Microsoft Learn

Best Regards,

Neeko Tang

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

 

[Jayee]

Hi @DZet ,

Try this not 100% sure about result if not work , can you share the .pbix file (without sensitive data) to us.

Testing =
RANKX (
FILTER (
ALL (
'Table'[OrderedItemKey],
'Table'[OrderNo],
'Table'[OrderDt],
'Table'[CustomerKey]
),
'Table'[OrderedItemKey] = MAX ( 'Table'[OrderedItemKey] )
),
TOTALYTD ( SUM ( 'Table'[Amount] ) ,'Date'[OrderDt])
)

[DZet]

Hi Jayee,

thanks for your help, but it's not correct. Item key is not important, we need to rank the sell on the Order level.
Here is the link to the pbix file: https://www.dropbox.com/s/7jp7ke7fxmpyk78/PBI%20Task.pbix?dl=0

I created a measure with your code and you can see it in the table on the left side. On the right, there is a SellRank, that I created, but doesn't work with the bottom date range properly.

Thanks in advance to anyone who could help! 🙂