# DAX: DISTINCTCOUNT with RANX and ADDCOLUMNS problem

• ### Question

• Hello All
I'm trying to achieve the following:
1. identify the top 2 best selling SKUs
2. check how many above SKUs are stockout in any stores.
My approach:

[Total Sales Qty]

SUM(SalesTbl[Qty])

[Rank]

IF(NOT(ISBLANK([Total Sales Qty]))&&HASONEVALUE(SkuTbl[SKU]),

RANKX(ALL(SkuTbl[SKU]),[Total Sales Qty],,,Dense),

BLANK()

)

[Count of Top 2 Sku with 0 qty in each store]

CALCULATE(

DISTINCTCOUNT(InvTbl[SKU]),

InvTbl[On Hand Qty]=0,

FILTER(                               <= select ONLY the top 2 SKUs and ignore the rest

"Ranking",CALCULATE([Rank])

),

[Ranking]<=2&&NOT(ISBLANK([Total Sales Qty]))

)

)

I'm hoping to get the following result

 Store Count of Top 2 Sku has 0 qty in each store Store1 C3 1 D4 1 Store2 C3 1 Store3 D4 1 Grand Total 2

Any help would be great!

Thanks

Sample Data
 Pivot Table 1 SKU Total Sales Qty Rank C3 30 1 D4 11 2 B2 8 3 A1 5 4 Grand Total 54

 Sales Table SKU Store Qty 01/01/2016 A1 Store1 3 01/01/2016 B2 Store1 4 01/01/2016 D4 Store1 5 01/01/2016 A1 Store2 1 01/01/2016 B2 Store2 2 01/01/2016 C3 Store2 16 01/01/2016 D4 Store2 4 01/01/2016 A1 Store3 1 01/01/2016 B2 Store3 2 01/01/2016 C3 Store3 14 01/01/2016 D4 Store3 2

 Inv Table Store SKU On Hand Qty Store1 A1 1 Store1 B2 0 Store1 C3 0 Store1 D4 0 Store1 E5 1 Store2 A1 0 Store2 B2 0 Store2 C3 0 Store2 D4 1 Store2 E5 0 Store3 A1 1 Store3 B2 0 Store3 C3 1 Store3 D4 0 Store3 E5 0

• Edited by Wednesday, February 3, 2016 10:46 PM
Wednesday, February 3, 2016 10:53 AM

• Your formulas are hard to read, but I miss something like ALL(Stores) somewhere which would cause the rank to be calculated over all stores. I suspect that the top 2 are now calculated per store.

And I think your calculations need a lot of simplification. You can calculate the top 2 customers using the TOPN function with appropriate arguments, and use that as a filter to calculate the out-of-stock skus. Something like

COUNTROWS(CALCULATETABLE(FILTER(SKUs,[CurrentStock]=0),TOPN(2,SKUs,[Sales])))

Wednesday, February 3, 2016 5:28 PM
• Pivot tbles can do this natively with TopN filters on the right fields. Any reason you need it to be in a measure?

Wednesday, February 3, 2016 8:45 PM
• Good question. 1. the measure will be part of the dashboard report component. 2. RankX/TopN are just the stepping stone so I can perform ABC analysis to monitor the stockout status in each category/store. utimately i will add the time intelligence to the measure for better usability.  ie. among 500 stores only these x stores stockout the these topN products for past z days.

Wednesday, February 3, 2016 10:19 PM
• No, I was referring to my formula, not yours:

COUNTROWS(CALCULATETABLE(FILTER(SKUs,[CurrentStock]=0),TOPN(2,ALL(SKUs),[Sales])))

It would help to show what the result is with this formula. You say it doesn't return the expected results, but what does it return? I don't have the model here...

Thursday, February 4, 2016 7:09 AM
• OK, so C3 and D4 are skus that are actually in the pivot table - you may have to do the TOPN over ALL(SKUs) instead.
Wednesday, February 3, 2016 9:53 PM

### All replies

• Your formulas are hard to read, but I miss something like ALL(Stores) somewhere which would cause the rank to be calculated over all stores. I suspect that the top 2 are now calculated per store.

And I think your calculations need a lot of simplification. You can calculate the top 2 customers using the TOPN function with appropriate arguments, and use that as a filter to calculate the out-of-stock skus. Something like

COUNTROWS(CALCULATETABLE(FILTER(SKUs,[CurrentStock]=0),TOPN(2,SKUs,[Sales])))

Wednesday, February 3, 2016 5:28 PM
• Hello Michiel

Thank you for your valuable input. The formula you suggested works but the result is not what I'm expecting.

I'm hoping to see the following

 Store Count of Top 2 Sku has 0 qty in each store Store1 C3 1 D4 1 Store2 C3 1 Store3 D4 1 Grand Total 2

Wednesday, February 3, 2016 7:50 PM
• Pivot tbles can do this natively with TopN filters on the right fields. Any reason you need it to be in a measure?

Wednesday, February 3, 2016 8:45 PM
• OK, so C3 and D4 are skus that are actually in the pivot table - you may have to do the TOPN over ALL(SKUs) instead.
Wednesday, February 3, 2016 9:53 PM
• Good question. 1. the measure will be part of the dashboard report component. 2. RankX/TopN are just the stepping stone so I can perform ABC analysis to monitor the stockout status in each category/store. utimately i will add the time intelligence to the measure for better usability.  ie. among 500 stores only these x stores stockout the these topN products for past z days.

Wednesday, February 3, 2016 10:19 PM
• Hello Michiel

Are you referring to the [Rank] measure? if so the formula doesnt work after the All(SKU) replaced with TOPN.

With current measure [Count of Top 2 SKU with 0 qty in each store] will result like this:

 Store Count of Top 2 Sku with 0 qty in each store Store1 B2 1 D4 1 Store2 C3 1 Store3 B2 1 D4 1 Grand Total 2

However, I know item B2 is not the Top2 SKU. So i'm expecting the one below:

 Store Count of Top 2 Sku has 0 qty in each store Store1 C3 1 D4 1 Store2 C3 1 Store3 D4 1 Grand Total 2

Wednesday, February 3, 2016 10:46 PM
• No, I was referring to my formula, not yours:

COUNTROWS(CALCULATETABLE(FILTER(SKUs,[CurrentStock]=0),TOPN(2,ALL(SKUs),[Sales])))

It would help to show what the result is with this formula. You say it doesn't return the expected results, but what does it return? I don't have the model here...

Thursday, February 4, 2016 7:09 AM