Forum Discussion

merath01's avatar
merath01
Regular Visitor
6 years ago

Create a formula for cumulative total

Hello, I am trying to create a measure (or column) that calculates the rolling cumulative sales in a linear format for a table or matrix.  I know the sales and create a formula for the Cumulative Sa...
Need Help
edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

This works. My weeks aren't sorted properly, but you can see weeks 3, 4, 5 match your numbers.

 

 

 

Linear Total =
VAR varAverage =
    AVERAGEX(
        ALL( 'Table' ),
        'Table'[Sales]
    )
VAR varCurrentWeek =
    MAX( 'Table'[Week] )
VAR varCurrentWeekNo =
    VALUE(
        RIGHT(
            varCurrentWeek,
            LEN( varCurrentWeek )
                - FIND(
                    " ",
                    varCurrentWeek
                )
        )
    )
VAR Result = varAverage * varCurrentWeekNo
RETURN
	Result

 

merath01's avatar
merath01
Regular Visitor
6 years ago

Thanks for your assistance.  I still can't get it to work with my real data, the numbers are too low.  I did notice that the total on your example did not equal the 242 total sales, which it should.  Any thoughts?

 

Thanks again!