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
cobus_19
Frequent Visitor

Summing a running total measure

Hi

I have created the following measure to calculate a running total

Sales Ann Proj Running = 
CALCULATE (
    [Sales Ann Proj],
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)


And it works wonderfully, I can see my running total per month.
I then add these monthly running totals to other measures to get my effective revenue per month.
However, when I want to see an aggregated overview of my revenue for the year I notice something is off.
That is because as soon as I move beyond a monthly view it is just giving me the latest running total instead of an aggregate of all the monthly running totals.

To hopefully make it more clear:

 

Capture.PNG

 

I need a way to get the sum of all monthly "Sales Ann Proj Running" instead of 119,882 which the running sum until the end of the year.

Regards
Cobus

3 REPLIES 3
v-jiascu-msft
Employee
Employee

@cobus_19

 

Hi,

 

Do you mean this: 1 + (1 + 2) + (1 + 2 + 3) + (1 + 2 + 3 + 4) + … = Total. It seems that it doesn't make any sense for sales. There is a workaround you can try. Add a column in Date table. Use this formula as calculated column. Finally, you can add this column in the report.

Sales Ann Proj Running =
CALCULATE (
    [Sales Ann Proj],
    FILTER (
        'Date'[Date],
        'Date'[Date] <= EARLIER ( 'Date'[Date] )
    )
)

 

Summing a running total measure .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-jiascu-msft


Version:1.0 StartHTML:000000256 EndHTML:000003106 StartFragment:000002019 EndFragment:000003074 StartSelection:000002019 EndSelection:000003070 SourceURL:https://community.powerbi.com/t5/forums/replypage/board-id/power-bi-designer/message-id/79431

@v-jiascu-msft

Hi Dale,

 

Thanks for the reply.
Perhaps I should explain what it is for better.

A portion of each month's sales are attributed as "annuity" - this means 15% of each months sales get carried over for all months following until the member cancels their "subscription"

i.e.
2017-01: Sales of R1000
2017-02: Annuity of R150
2017-03: Annuity of R150
2017-04: Annuity of R150
etc

Thus my revenue at the end of the year is:
Sales + Annuity
And because my table only includes the original sale date I have created a running annuity column.
It all works very well until I need to see the total.

I have tried your solution
Unfortunately I don't want the running total per date, but per month


Capture.PNG

Regards

Cobus

@cobus_19

 

Hi,

 

At least the result of 2017-03 should be right. So there may be something context trick in the formula of  [Sales Ann Proj]. Could you please post the formula here? The idea is very simple. We can't get the total of a measure, but we can get the total of a column. So we adjust the formula to run it as a calculated column. Then we will get the total in the report. Please have a try.

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.