Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Vote for your favorite vizzies from the Power BI Dataviz World Championship submissions. Vote now!

Reply
Dunner2020
Post Prodigy
Post Prodigy

Use of measure in calculating running total

‎09-16-2020 05:55 PM

Hi there, 

 

I have data which looks like as follow:

 

IncidentTimeTotalConsumerMinutesTotal MinutesDateTime (NZST)Month
Sunday, 5 April 202029568917556/04/20201:28:00 AMApril
Sunday, 5 April 202023046917556/04/20201:28:00 AMApril
Sunday, 5 April 20201173917556/04/20207:03:00 AMApril
Monday, 6 April 2020232917557/04/20209:13:00 AMApril
Thursday, 9 April 202010057917559/04/20207:08:00 PMApril
Thursday, 9 April 20202261917559/04/20207:08:00 PMApril
Thursday, 9 April 20207488917559/04/20205:21:00 PMApril
Monday, 13 April 20202909175514/04/202010:59:00 AMApril
Monday, 6 April 2020315917557/04/20209:30:00 AMApril
Monday, 6 April 2020174917557/04/20201:18:00 PMApril
Monday, 20 April 2020101669175521/04/202011:36:00 AMApril
Thursday, 23 April 202013769175523/04/202011:27:00 PMApril
Wednesday, 29 April 2020133389175529/04/20204:38:00 PMApril
Wednesday, 29 April 202051309175529/04/20203:01:00 PMApril
Wednesday, 29 April 202095949175529/04/20203:01:00 PMApril
Tuesday, 7 April 202015075917557/04/20205:33:00 PMApril
Tuesday, 14 April 202037029175514/04/20202:22:00 PMApril
Tuesday, 14 April 20201729175515/04/202010:52:00 AMApril
Tuesday, 14 April 2020246849175514/04/20202:22:00 PMApril
Tuesday, 14 April 202020689175514/04/20202:22:00 PMApril
Tuesday, 14 April 2020499509175514/04/20202:22:00 PMApril
Tuesday, 14 April 2020196609175514/04/20201:56:00 PMApril
Friday, 24 April 20204419175524/04/20208:32:00 PMApril
Monday, 4 May 20208616917555/05/20209:06:00 AMMay
Monday, 4 May 20202730917555/05/20209:06:00 AMMay
Tuesday, 5 May 20204644917555/05/20201:30:00 PMMay
Tuesday, 5 May 20201638917555/05/20205:33:00 PMMay
Tuesday, 5 May 20202975917555/05/20206:39:00 PMMay
Tuesday, 5 May 2020260917555/05/202010:00:00 PMMay
Tuesday, 5 May 20203965917555/05/20206:39:00 PMMay
Tuesday, 5 May 20201044917555/05/20206:39:00 PMMay
Tuesday, 5 May 20205474917555/05/20206:39:00 PMMay
Tuesday, 5 May 2020130917555/05/202010:52:00 PMMay
Tuesday, 5 May 20203796917556/05/202010:21:00 AMMay
Tuesday, 5 May 202050132917556/05/202010:21:00 AMMay
Tuesday, 5 May 202024780917556/05/202010:21:00 AMMay
Tuesday, 5 May 202059740917556/05/202010:21:00 AMMay
Tuesday, 5 May 2020364917556/05/202011:37:00 AMMay
Tuesday, 5 May 20203503917556/05/202010:21:00 AMMay
Tuesday, 5 May 202017664917556/05/202010:21:00 AMMay
Wednesday, 6 May 20209177917557/05/20209:12:00 AMMay
Wednesday, 6 May 2020888917557/05/20209:12:00 AMMay
Wednesday, 6 May 2020560917557/05/20209:36:00 AMMay
Saturday, 9 May 202049209175510/05/20209:04:00 AMMay
Tuesday, 5 May 20203835917556/05/20209:11:00 AMMay
Tuesday, 5 May 20201850917556/05/20209:37:00 AMMay
Tuesday, 12 May 202073359175513/05/202011:34:00 AMMay
Tuesday, 12 May 20204189175513/05/202011:34:00 AMMay
Wednesday, 13 May 2020189289175513/05/202012:40:00 PMMay
Wednesday, 13 May 202070009175513/05/202012:40:00 PMMay
Wednesday, 13 May 202082289175513/05/202012:40:00 PMMay
Thursday, 14 May 202037659175515/05/20207:20:00 AMMay
Thursday, 14 May 20202529175515/05/202010:29:00 AMMay
Thursday, 14 May 20201269175515/05/202011:22:00 AMMay
Thursday, 14 May 202025609175515/05/202010:42:00 AMMay
Thursday, 14 May 202025609175515/05/202010:59:00 AMMay
Thursday, 14 May 2020849175515/05/202010:42:00 AMMay
Wednesday, 6 May 202026145917557/05/20208:45:00 AMMay
Thursday, 14 May 2020169175516/05/20202:06:00 PMMay
Thursday, 14 May 202089175515/05/20207:44:00 AMMay
Tuesday, 19 May 20204289175520/05/20209:36:00 AMMay
Wednesday, 20 May 202067369175521/05/20204:28:00 AMMay
Wednesday, 20 May 2020934669175521/05/20206:45:00 AMMay
Friday, 22 May 20201779175522/05/20203:11:00 PMMay
Monday, 25 May 20207609175525/05/20202:11:00 PMMay
Monday, 25 May 202023409175525/05/20202:11:00 PMMay
Monday, 25 May 202058839175525/05/20202:11:00 PMMay
Tuesday, 26 May 2020157329175527/05/20209:02:00 AMMay
Tuesday, 26 May 202013389175527/05/20209:02:00 AMMay
Tuesday, 26 May 2020264969175527/05/20209:03:00 AMMay
Tuesday, 26 May 2020290089175527/05/20208:45:00 AMMay
Thursday, 28 May 202019849175528/05/20202:40:00 PMMay
Thursday, 28 May 2020141559175528/05/20202:40:00 PMMay
Wednesday, 27 May 202020809175527/05/20206:13:00 PMMay
Wednesday, 27 May 20201599175527/05/20206:13:00 PMMay

 

I first created a measure that calculates the consumed minute by dividing TotalConsumerMinutes by TotalMinutes as follow:

Consumed minutes = MAX('Table'[TotalConsumerMinutes]) /MAX('Table'[Total Minutes])
 
Then I want to create a Running total of consumed minutes which I wanted to show on the line graph by month.
Running total = calculate( sum([Consumed Minutes]), Filter(Table, Table['Date'] <=Max(Table[Date]))
 
However, above-mentioned statement gave me an error that only accepts a column reference as an argument. I think it because we can not apply aggregators on the aggregator. I am not sure how I use the first measure in the second measure to calculate the running total. Any guidance would be really appreciated.
Message 1 of 7
975 Views
0
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

‎09-16-2020 06:17 PM

@Dunner2020 , Try like

 

Running total = calculate( sumX('Table',[Consumed Minutes]), Filter(Table, Table[Date] <=Max(Table[Date]))

or

Running total = calculate( MAX('Table'[TotalConsumerMinutes]) /MAX('Table'[Total Minutes]), Filter(Table, Table[Date] <=Max(Table[Date]))

 

But as you have overlapping dates you might see some issues

 

have column like

datetime = [Date]+ [Time (NZST)] + time(0,0,rand()) // You need check if rand work second or not

datetime = [Date]+ [Time (NZST)] + rand()/1000 // You need check if rand work second or not

and use this new field

 

Running total = calculate( sumX('Table',[Consumed Minutes]), Filter(Table, Table[datetime] <=Max(Table[datetime ]))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

Message 2 of 7
942 Views
6 REPLIES 6
amitchandak
Super User
Super User

‎09-16-2020 06:17 PM

@Dunner2020 , Try like

 

Running total = calculate( sumX('Table',[Consumed Minutes]), Filter(Table, Table[Date] <=Max(Table[Date]))

or

Running total = calculate( MAX('Table'[TotalConsumerMinutes]) /MAX('Table'[Total Minutes]), Filter(Table, Table[Date] <=Max(Table[Date]))

 

But as you have overlapping dates you might see some issues

 

have column like

datetime = [Date]+ [Time (NZST)] + time(0,0,rand()) // You need check if rand work second or not

datetime = [Date]+ [Time (NZST)] + rand()/1000 // You need check if rand work second or not

and use this new field

 

Running total = calculate( sumX('Table',[Consumed Minutes]), Filter(Table, Table[datetime] <=Max(Table[datetime ]))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 2 of 7
943 Views
Dunner2020
Post Prodigy
Post Prodigy
In response to amitchandak

‎09-16-2020 07:39 PM

@amitchandak ,

 

That is the output of my last reply

 

myasir_0-1600310361372.png

As you can see that TotalConsumerMinutes and debug columns have the same value which should not be the case.

Message 4 of 7
927 Views
0
amitchandak
Super User
Super User
In response to Dunner2020

‎09-16-2020 08:25 PM

@Dunner2020 , Please see this file. Both Column and measure are running totals. As Date was not detected as I used Incident date(dd/mm format, my system use mm/yy) 

 

See if this can help

 

 

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
cummTimedate.pbix
Message 5 of 7
914 Views
0
Dunner2020
Post Prodigy
Post Prodigy
In response to amitchandak

‎09-16-2020 08:58 PM

@amitchandak , Your file produces the desired result. However, when I did the same thing on my file it ended up the same old result. Do you think its because of the different date formats? I could not get the date format comments from your last post.

Message 6 of 7
908 Views
0
amitchandak
Super User
Super User
In response to Dunner2020

‎09-16-2020 09:10 PM

@Dunner2020 , Date should be detected as date after that format does not matter. I also added a random number in Datetime column I created. Please check datetime column I created 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 7 of 7
903 Views
0
Dunner2020
Post Prodigy
Post Prodigy
In response to amitchandak

‎09-16-2020 07:33 PM

@amitchandak , Looks like that no calculation worked. I tried to debug the issue using following command:

 

CONCATENATEX(FILTER('Table','Table'[Date]<=MAX('Table'[Date])), sum([TotalConsumerMinutes]),",")
 
and found that sum of TotalConsumerMinutes shows the value of current row. It did not add up the value of TotalConsumerMinutes from previous dates.
Message 3 of 7
933 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

Vote for your favorite vizzies from the Power BI World Championship submissions!

Sticker Challenge 2026 Carousel

Join our Community Sticker Challenge 2026

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

January Power BI Update Carousel

Power BI Monthly Update - January 2026

Check out the January 2026 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All