topic Re: Optimization of Product Ranking Measure in DAX Commands and Tips

Optimization of Product Ranking Measure

George1973 — Mon, 19 Jul 2021 11:09:12 GMT

Dear Community,

I have the measure calculating product sales ranking adding to them ABCDX priorities like given below:

Rank for Sales on Products = VAR RunningTotal_Prod_Sales=CALCULATE([Total Sales],TOPN([TotProduct Ranking per Sales],ALLSELECTED(Tables_Products),[Total Sales])) VAR GrandTotal_for_TotalSales=CALCULATE([Total Sales],ALLSELECTED(Tables_Products)) VAR RunnigTotal_ProdSales_Shares=DIVIDE(RunningTotal_Prod_Sales,GrandTotal_for_TotalSales,0) Return SWITCH(TRUE(),RunnigTotal_ProdSales_Shares<=0.8,"A", RunnigTotal_ProdSales_Shares>0.8 && RunnigTotal_ProdSales_Shares<=0.95,"B", RunnigTotal_ProdSales_Shares>0.95 && RunnigTotal_ProdSales_Shares<=0.99,"C", RunnigTotal_ProdSales_Shares>0.99 && RunnigTotal_ProdSales_Shares<=1,"D","X")

The measure works corectly, however the speed is too low, due to high number of recordset - apprx. 1 min!

Please suggest to way how to optimize the measure.

Thanks in advance,

Re: Optimization of Product Ranking Measure

mahoneypat — Mon, 19 Jul 2021 12:06:06 GMT

Please try it with these changes

Rank for Sales on Products =
VAR summary =
    ADDCOLUMNS ( ALLSELECTED ( Tables_Products ), "cSales", [Total Sales] ) // use a single column if possible in ALLSELECTED like Tables_Products[ProductID]
VAR RunnigTotal_Prodsales_Share =
    SUMX (
        TOPN ( [TotProduct Ranking per Sales], summary, [cSales], DESC ),
        [cSales]
    )
VAR GrandTotal_for_TotalSales =
    SUMX ( summary, [cSales] )
VAR RunnigTotal_ProdSales_Shares =
    DIVIDE ( RunningTotal_Prod_Sales, GrandTotal_for_TotalSales, 0 )
RETURN
    SWITCH (
        TRUE (),
        RunnigTotal_ProdSales_Shares <= 0.8, "A",
        RunnigTotal_ProdSales_Shares > 0.8
            && RunnigTotal_ProdSales_Shares <= 0.95, "B",
        RunnigTotal_ProdSales_Shares > 0.95
            && RunnigTotal_ProdSales_Shares <= 0.99, "C",
        RunnigTotal_ProdSales_Shares > 0.99
            && RunnigTotal_ProdSales_Shares <= 1, "D",
        "X"
    )

Pat

Re: Optimization of Product Ranking Measure

George1973 — Mon, 19 Jul 2021 13:02:16 GMT

Hi,
Thanks a lot for the provided solution. I think there is a little mistake here:

Is the first viriable there should be "RunningTotal_Prod_Sales" instead of "RunnigTotal_Prodsales_Share"..

If I'm mistaken sorry for that 🙂

The formula speed has been optimized indeed by 3 times, but as for the results (outcome) I have to check it once again, there seems to be some deviations

Re: Optimization of Product Ranking Measure

George1973 — Mon, 19 Jul 2021 13:09:45 GMT

Hi Again,

Yes, the formula works great! As communicated by 3 times faster!

Thanks a lot for your support!