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: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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
775 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
742 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
743 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
727 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
714 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
708 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
703 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
733 Views
0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 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
Top Solution Authors
View All
Top Kudoed Authors
View All