RANKX and Date Slicer issue

[kylebennett6]

I have a matrix visualization that's aimed at providing the following information:

• Account representatives (row)

• Account rep's accounts (row)

• Each account's top 5 SKUs (InvtID) by revenue (Amount) (row)

Key Value fields are:

• Revenues by SKU (Amount)

• SKU rank (1 being the highest revenue, 5 being the lowest, coded as a Measure)

The key measure involved here is a RANKX, coded as follows, as a Measure to provide the SKU ranks:

InvRnkDESC = RANKX(ALL('Purchase Table'[Invtid]), CALCULATE(SUM('Purchase Table'[Amount])),,DESC,Dense)

The goal:

To visualize in a matrix each account representative's accounts, those accounts' top 5 SKUs by revenue, sliced by a dynamic date range. So if I change the date slicer to a different range, it will ideally re-summarize the revenues, and re-rank them.

The problem:

When the date slicer I've included in the dashboard ranges the entire date span of the data table (first image below), the SKUs are ranked appropriately, 1 through 5 by revenues by account by account representative. However, as soon as I shrink the date slicer to capture a trailing 12 month, for example, the SKU rankings get odd and repeat themselves non-sensically. For example, four starkly different revenue amounts will all be ranked as #1 (second image below).

Digging into this by transforming the matrix into a table and adding a Date column led me to discover that the measure for InvRnkDESC is ranking based on a date purchase. So for example, if 3 purchases are made on the same day, it will rank the largest revenue as #1, the middle as #2, and the lowest as #3. It will repeat the ranking on different days. So there is something in my code above that's not controlling for a dynamic date range and I don't know how to fix it.

To visualize the issue:

This is correct -

This is incorrect -

Does anyone have any insight into modifying this to control for a dynamic date range in the measure? I was wondering, does the measure for ranking actually need to be a column instead?

[kylebennett6]

@bcdobbs 

Sample .pbix file is here.

Replacing ALL with ALLSELECTED as you suggested above did not work. It removed all ranks less than 1 and still ranked them non-sensically, issuing 3 different revenue values a ranking of #1.

InvtID is the same as SKU. It is a product code used to identify distinct products. I do not pull it from a dimension or related table. All of the data is contained in a single table, called Purchase Table, which is a row by row entry of customer orders. Each row essentially corresponds to unique products purchased on unique dates. Roughly, it looks like this (omitting various unrelated columns):

Date	Order Number	Customer ID	Sales ID	InvtID	Amount ($)
10/20/2020	1	INV010	100	1015D	125
11/19/2020	2	INV010	100	2090LK	75
12/1/2020	3	INV010	100	2090LK	75
2/19/2021	4	INV010	100	1015D	125
2/19/2021	4	INV010	100	2090LK	75
2/19/2021	4	INV010	100	3045AR	25
3/22/2021	5	INV010	100	1015D	125
3/22/2021	5	INV010	100	2090LK	75
5/25/2021	6	INV010	100	1015D	125
5/25/2021	6	INV010	100	3045AR	25
6/2/2021	7	INV010	100	2090LK	75
7/16/2021	8	INV010	100	2090LK	75

The idea of this table is to capture the total revenue amounts per unique product per unique order number.

Now imagine this data repeats for every account belonging to every unique Sales ID for every unique order placed over the course of ~3 years. Dataset is over 2.2M rows.

Using the above example, it's clear that over the period between 10/20/2020 - 7/16/2021 (entirety of the table), I want my visualization to rank the SKUs as follows, based on associated revenue amounts:

1. 1015D ($500)
2. 2090LK ($450)
3. 3045AR ($50)

And it does that, when the date slicer includes the entire date range of the table.

However, if I sliced the date down to 11/19/2020 - 7/16/2021 (omitting order #1 essentially), the rankings should be:

1. 2090LK ($450)
2. 1015D ($375)
3. 3045AR ($50)

Instead, the current visualization will give multiple SKUs the same ranking because from what I can tell, it begins to summarize product revenue by each date. So, it will calculate a ranking for 11/19, 12/1, 2/19, and so on. This produces a ranking like this...

1. 2090LK ($450)

1. 1015D ($375)

3. 3045AR ($50)

...because on any given day, the highest SKU is either 2090LK or 1015D.

I need the measure to basically recalculate the rankings based on the date slicer and then update the matrix accordingly.

[smpa01]

@kylebennett6  can you provide a sample pbix please?

[kylebennett6]

See here.

[smpa01]

@kylebennett6  I don't hink this is represntative of the issue you described

[kylebennett6]

How so? The .pbix file I provided includes demo data that contains the exact columns I have in my actual dataset.

The two visualizations that are present in the demo .pbix file are a date slicer and a matrix, both of which are directly representative of the visuals in my actual .pbix file.

When the entire date range is selected, the rankings are calculated correctly. When the date slicer is shortened, the rankings become non-sensical. Please, give it a try.

I need the rankings measure to update with the date slicer.

[bcdobbs]

I might need a demo pbix with some dummy data in but the same table structure.

However see what happens if you swap:

ALL('Purchase Table'[Invtid])

for 

ALLSELECTED('Purchase Table')

[bcdobbs]

Suspect that won't work. If it doesn't can you explain what Invtid is? Does it have a related dimension table?

[kylebennett6]

I had responded to you with a sample .pbix file and a detailed response, but it got removed and flagged as spam and now I can't find it. Is there any way the moderator can recover and re-post the response? Do you know how to contact them directly?

[bcdobbs]

I can still see your pbix and downloaded it.

[kylebennett6]

I tried the code you provided using ALLEXCEPT and that did not work either. Gave the same rankings regardless of date slicer.

[bcdobbs]

Have a look at this.
DAX is simpler but I started by separating out into dimension tables.
Sample File 

Going to have a look now if I can do it without separating the tables out.

[bcdobbs]

This seems to work on my end without splitting it into a star schema (consider the example I sent in the file, generally makes things easier)

InvRnkDESC = 
    RANKX(
        CALCULATETABLE (
            SUMMARIZE(
                'Sample Table',
                'Sample Table'[Sales ID],
                'Sample Table'[Customer ID],
                'Sample Table'[Invt ID]
            ),  
            ALLEXCEPT ( 'Sample Table', 'Sample Table'[Date] )
        ),
        CALCULATE ( SUM ('Sample Table'[Amount ($)] ) ),
        ,
        DESC,
        Dense
    )

[kylebennett6]

That definitely ranks accurately now...but I really hope this can be accomplished without breaking dimensions out into tables. This might get extremely messy with our master .pbix files.

[bcdobbs]

Hoping the last dax I sent does it in one. Seemed to give same values as the star schema.

[kylebennett6]

The DAX you sent seems to work fine in the small demo data I provided, but it does not extend to the master dataset I'm working with and I don't know why.

Here's a snippet of the code translated to my master .pbix. I've renamed the table and slightly adjusted the columns, but it's pretty clear what means what.

When I execute this code and check the matrix visual, I'm getting some really odd values. Here is where I was before I posted this question:

Now here is what the output is after submitting new DAX:

I'm only speculating because I'm a rookie here, but it looks like the RANKX function is applying InvtID ranks across all customers and all orders in the table.

Just in case I wasn't clear, I need an InvtID ranking by customer. Therefore, if I filter the IncRnkDESC column to show less than or equal to 5, I should get a 1 through 5 ranking for each unique Customer ID.

I feel like the DAX code you provided works in the demo data because the demo data only features 1 customer ID. The master dataset has thousands of customer IDs.

[bcdobbs]

Yeah, that would be the case your sample data needed a little more depth!

Try adding the SalesId and CustomerId into the all except.

[kylebennett6]

I'm exploring both options: DAX and star schema. I'm working in the master .pbix file now and will review my draft report for accuracy. I'll keep you posted and respond back here with a solution flag if we're all good.

[kylebennett6 · Accepted solution]

Here is the solution:

• The DAX for the key measure should be - 
  • InvRnkDESC = RANKX(ALLSELECTED('Purchase Table'[Invtid]), CALCULATE(SUM('Purchase Table'[Amount])),,DESC,Dense)
  • The only change here is to go from ALL to ALLSELECTED (original DAX in my post)
• The main data table 'Purchase Table' needs to have 1-to-many model connections to each of the following tables:
  • A Customer ID master that defines all of the unique customer IDs
  • An item master that defines all of the unique InvtIDs
  • A sales ID master that defines all of the unique SalesIDs
  • A calendar master that defines all of the unique dates in the range of the slicer you intend to use in your report
    • This is probably the key since my report was generating strange numbers for the InvtRnkDESC as soon as I sliced the date range to something smaller than the full range of the dataset

Of note, SalesID, CustomerID, and InvtID are all rows in the matrix visual I'm trying to build. I'm not entirely sure if there is relevance between this and the fact that I needed to connect all of them 1-to-many in the model, but I wasn't able to generate a solution until I did this.

I want to thank @bcdobbs for his assistance on helping me navigate this issue. This is the first report I've built against our dataset that utilizes dynamic date ranges and with this information in hand, I imagine we can build more much more easily now.

[kylebennett6]

Sample .pbix file is here.

Replacing ALL with ALLSELECTED as you suggested above did not work. It removed all ranks less than 1 and still ranked them non-sensically, issuing 3 different revenue values a ranking of #1.

InvtID is the same as SKU. It is a product code used to identify distinct products. I do not pull it from a dimension or related table. All of the data is contained in a single table, called Purchase Table, which is a row by row entry of customer orders. Each row essentially corresponds to unique products purchased on unique dates. Roughly, it looks like this (omitting various unrelated columns):

Date	Order Number	Customer ID	Sales ID	InvtID	Amount ($)
10/20/2020	1	INV010	100	1015D	125
11/19/2020	2	INV010	100	2090LK	75
12/1/2020	3	INV010	100	2090LK	75
2/19/2021	4	INV010	100	1015D	125
2/19/2021	4	INV010	100	2090LK	75
2/19/2021	4	INV010	100	3045AR	25
3/22/2021	5	INV010	100	1015D	125
3/22/2021	5	INV010	100	2090LK	75
5/25/2021	6	INV010	100	1015D	125
5/25/2021	6	INV010	100	3045AR	25
6/2/2021	7	INV010	100	2090LK	75
7/16/2021	8	INV010	100	2090LK	75

The idea of this table is to capture the total revenue amounts per unique product per unique order number.

Now imagine this data repeats for every account belonging to every unique Sales ID for every unique order placed over the course of ~3 years. Dataset is over 2.2M rows.

Using the above example, it's clear that over the period between 10/20/2020 - 7/16/2021 (entirety of the table), I want my visualization to rank the SKUs as follows, based on associated revenue amounts:

1. 1015D ($500)
2. 2090LK ($450)
3. 3045AR ($50)

And it does that, when the date slicer includes the entire date range of the table.

However, if I sliced the date down to 11/19/2020 - 7/16/2021 (omitting order #1 essentially), the rankings should be:

1. 2090LK ($450)
2. 1015D ($375)
3. 3045AR ($50)

Instead, the current visualization will give multiple SKUs the same ranking because from what I can tell, it begins to summarize product revenue by each date. So, it will calculate a ranking for 11/19, 12/1, 2/19, and so on. This produces a ranking like this...

1. 2090LK ($450)

1. 1015D ($375)

3. 3045AR ($50)

...because on any given day, the highest SKU is either 2090LK or 1015D.

I need the measure to basically recalculate the rankings based on the date slicer and then update the matrix accordingly.