Forum Discussion

George1973's avatar
George1973
Icon for Helper V rankHelper 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 ...
  • 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

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft 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