Rank Sales by Month and Year

DAX DAX Calculations
1

Hello,

On the Rank Tab, I have a table that shows the delta between selected month sales and a trailing 3 month average sales. This is all being driven by the measure “Rank 3 Month Delta”.

I am trying to use the virtual table inside the measure to obtain the rank using the 3 month delta calculation. When I try replacing Sumx with Rankx, I receive an error on my table.

Any help is very much appreciated.

Mark
AR Aging Example.pbix (992.5 KB)
AR Aging Sample Data.xlsx (8.1 MB)

2

Hi @msedlak,

Give this a go instead. You can use SUMMARIZE on extended table columns, so you don’t require a CROSSJOIN. It’s important to include ALLSELECTED otherwise you are ranking in filter context of your visual and each line will evaluate to 1.

Rank 3 Month Delta v2 = 
    RANKX (
        SUMMARIZE( ALLSELECTED( 'Invoices' ), 'Invoices'[Customer #], 'Calendar'[MonthYear] ),
        [Avg Sales/Day] - [3 month Avg],
        , ,DENSE
    )

.
If you don’t want the Total Row to show 1, you can add IF ISINSCOPE, like below.

Rank 3 Month Delta v2 = 
IF( ISINSCOPE( Invoices[Customer #] ),    
    RANKX (
        SUMMARIZE( ALLSELECTED( 'Invoices' ), 'Invoices'[Customer #], 'Calendar'[MonthYear] ),
        [Avg Sales/Day] - [3 month Avg],
        , ,DENSE
    )
)

I hope this is helpful.

3

This works perfectly! So much simpler than I was making it. THANK YOU!!

4

Hi @Melissa ,

I am curios to know why we can not use the below code here :
image

pls enlighten me on this .

Thanks,
Anurag