Forum Discussion

George1973's avatar
George1973
Helper V
5 years ago
Solved

Optimization of Product Ranking Measure

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,

 

 

  • George1973's avatar
    George1973
    5 years ago

    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

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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

    • George1973's avatar
      George1973
      Helper V
      5 years ago

      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

      • George1973's avatar
        George1973
        Helper V
        5 years ago

        Hi Again,

         

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

         

        Thanks a lot for your support!