Create calculated column (top 10 / others) based on rank by Sales

[Anonymous]

Hi guys,

This thing drives me crazy so I thought maybe I could get some help here.

I have 2 tables : 1 fact with the sales and 1 dimension with the products (1 to * relation)

In the fact table I have different types of sales, so the measure to get the sales I need is :

Sales = CALCULATE(SUM('Table'[Amount]),'Table'[TypeOfSales] = "This One")

The Rank measure is giving me the expected result : 

Rank Product by Sales = RANKX(ALL(Product), _Measures[Sales],,DESC)

I created a calculated column as is :

Top 10 / Others =

VAR RankProd = RANKX(ALL(SKU), _Measures[Sales],,DESC)

return

IF(RankProd <= 10, "Top 10", "Others")

And this is what I get. As you can see, the measure gives me the right result but for some reasons, not the calculated column.

 

The goal of having a calculated column is to be able to group the products dynamically in a matrix and being able to expend it if needed.

Really appreciate your help here.

[PaulDBrown · Accepted solution]

A calculated column is not dynamic: it is calculated when the model is loaded. You need to use measures for this if you want it to be dynamic

[Anonymous]

Thanks Paul. I guess I'll have to find an other way to do it 🙂

[PaulDBrown]

Can you share some actual data? (Non-confidential)

[Anonymous]

Like a PBI file ? It might be complicated.

But technically, I am trying to calculate a forecast accuracy for the top 10 products. Obviously this top 10 is different by market, so needs to be dynamic. It seems to be something pretty straightforward, but i can't figure something out.

I am able to create a calculated measure to get the correct top 10 by market but then I can't figure out a way to isolate those products to calculate my forecast accuracy.

[PaulDBrown · Accepted solution]

I'm not too sure what the problem is. See if any of this helps:

Sample data:

Rank by product measure:

Rank Product =
RANKX ( ALL ( 'Product Table'[Product] ), [Sum Sales],, DESC, SKIP )

Sales for Top 3 products:

Sales top 3 = 
CALCULATE([Sum Sales], FILTER('Product Table', [Rank Product] <4))

List top 3 products:

Top 3 products by market = 
COUNTROWS(
    CALCULATETABLE(
        VALUES('Product Table'[Product]), 
        FILTER('Product Table', [Rank Product] <4)))

The CALCULATETABLE expression above will return the top 3 products

Sales top 3 = 
CALCULATE([Sum Sales], FILTER('Product Table', [Rank Product] <4))

within the filter context. So you can use the expression within measures. For example, you can also calculate the top 3 products by sales using:

Alternative top 3 sales = 
CALCULATE([Sum Sales], 
    CALCULATETABLE(
        VALUES('Product Table'[Product]), 
        FILTER('Product Table', [Rank Product] <4)))

So you can use the CALCULATETABLE expression for example for the forecast measure. You can also use the 

FILTER('Product Table', [Rank Product] <4)

Sample PBIX attached

[Anonymous]

OK, now I am even more confused 😐

When I do your calculation, it only works if I have only one column in the Product table.

If I have 2+, the top 5 doesn't work anymore. 

Only ID column in the product table

ID and SKU in the Product table

I wish I could share my PBIX but I can't find the option to do that ☹️

[PaulDBrown]

To share a PBIX (only if the information is not confidential; if it's confidential, please edit the condifential data) you can share a link to the file from a cloud service such as OneDrive, GoogleDrive, iCloud, Dropbox, Wetransfer...

[Anonymous]

Here you go. 

[PaulDBrown]

The link to the file doen't seem to work

[Anonymous]

Can you try again ?

Edit : Should work now 🙂

[PaulDBrown]

Thanks for the file. 

Seeing as you have more than one column in the SKU table, you need to specify the column you are filtering in the measures. So:

Sales top 5 ID =
CALCULATE (
    Sales[Sell Out Amount],
    FILTER ( VALUES ( SKU[Item_ID] ), [Rank ID] < 5 )
)

and

Sales top 5 SKU =
CALCULATE (
    Sales[Sell Out Amount],
    FILTER ( VALUES ( SKU[SKU] ), [Rank SKU] < 5 )
)

[Anonymous]

Thank you SO much Paul for your help. I was able to do everything I needed 👍

[PaulDBrown · Accepted solution]

A calculated column is not dynamic: it is calculated when the model is loaded. You need to use measures for this if you want it to be dynamic