Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
George1973
Resolver II
Resolver II

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,

 

 

1 ACCEPTED SOLUTION

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

George1973_0-1626699511298.png

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

View solution in original post

3 REPLIES 3
mahoneypat
Employee
Employee

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_TotalSales0 )
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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

George1973_0-1626699511298.png

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

Hi Again,

 

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

 

Thanks a lot for your support!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors