Forum Discussion

longpham03's avatar
longpham03
Regular Visitor
6 years ago

Cumulative Running Total

Hi all,

 

I have a sales table with 3 dimensions (Dates, Stores, Products).

 

I have a measure to calculate 6-month sales running total:

[SAS] = CALCULATE(SUM(L_Sales[Amount]),DATESINPERIOD(L_Date[Date],LASTDATE(L_Date[Date]),-6,MONTH))

 

Then its share:

[SAS_Share] = DIVIDE([SAS],CALCULATE([SAS],ALL(L_Products)))

 

My goal is to have a measure (or calculated column, please advise which option is better?) to rank products in terms of its SAS Share Cumulative. My understanding is to do this, I need to put measure [SAS_Share] in a calculated column so that I can preference in the [SAS_Cumulative] measure. This is what I have:

 

L_Products[SAS] = [SAS]

 

[SAS_Share_Cumulative] =
CALCULATE(
    [SAS_Share],
    FILTER(
        ALL(L_Products),
        ISONORAFTER(L_Products[SAS], MIN(L_Products[SAS]), ASC)
    )
)

 

[Product_Rank] = IF([SAS]=0,"E",IF([SAS_Share_Cumulative]<=.9,"A",IF([SAS_Share_Cumulative]<=.95,"B",IF([SAS_Share_Cumulative]<=.98,"C","D"))))

 

I think the [SAS_Share_Cumulative] is correct, but the L_Products[SAS] is incorrect. Its values are different from [SAS]. I think this is due to context transition when preferencing a measure in row contexts. My sales table does have an ID field (all rows are unique).

 

Please help me:

1. Is this the right way to find [SAS_Share_Cumulative] (through a calculated column in Products table), and should it be a measure or calculated column?

2. If so, what I have for L_Products[SAS] is incorrect, please help me fix it. Explanations would be greatly appreciated!

 

Thank you so so much!

Need Help

2 Replies