locked
DAX: DISTINCTCOUNT with RANX and ADDCOLUMNS problem RRS feed

  • 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

                                             ADDCOLUMNS(VALUES(SkuTbl[SKU]),

                                                                    "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 JohnL2K Wednesday, February 3, 2016 10:46 PM
    Wednesday, February 3, 2016 10:53 AM

Answers

  • 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
    Answerer
  • Pivot tbles can do this natively with TopN filters on the right fields. Any reason you need it to be in a measure?

    Check out the Power BI User Group of Philadelphia.
    Our next meeting is February 2 in Malvern, PA.

    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
    Answerer
  • 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
    Answerer

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
    Answerer
  • 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?

    Check out the Power BI User Group of Philadelphia.
    Our next meeting is February 2 in Malvern, PA.

    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
    Answerer
  • 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
    Answerer