Showing results for 
Search instead for 
Did you mean: 
billy218 Frequent Visitor
Frequent Visitor

Average of Sums

Hi all,


New to PowerBI and this on has been doing my head in so I am hoping for some help/guidance. 


I have data from our CRM and I am trying to calculate the total time a transaction was in a certain state (CRM_Flag).


I've already created the total hours column which calculates how long the transactions took until the next step. I've also created a column that flags wether it was the last step and thus should be excluded from calculations. 


This is an example of my data:



CRM_FlagWave_Trans_NODecisionDateTimeTotal HoursLast Step? 
No3658192019-06-27 14:53137.910
No3658192019-07-03 8:470.860
No3658192019-07-03 9:390.020
No3658192019-07-03 9:400.460
No3658192019-07-03 10:080.020
No3658192019-07-03 10:092.980
No3658192019-07-03 13:080.270
Yes3658192019-07-03 13:25337.350
Yes3658192019-07-17 14:46-1056.331
No3725452019-06-03 14:260.050
No3725452019-06-03 14:2917.590
No3725452019-06-04 8:052.710
No3725452019-06-04 10:472.150
Yes3725452019-06-04 12:56480.900
Yes3725452019-06-24 13:500.150
Yes3725452019-06-24 14:00-503.561


I am trying to sum all the No and Yes's by Transaction number, (excluding the 1's from LastStep), and then get a total average by CRM_flag. Something like this: 


CRM_Flag Average 'TotalHours'Median 'TotalHours'


Also, the transaction number has a region and product attached to it, so the end goal is to be able to distinguish this by region. 

I am thinking it is averagex, sumx, summarize, etc. but the exact application I get stuck on. 

Any help would be greatly appreciated!!

jthomson New Contributor
New Contributor

Re: Average of Sums

Hard to work out what you're trying to do here given your sample data doesn't coincide with your desired output at all, but DAX has average and median functions, I see no reason why just wrapping them in calculate and indicating last step is zero wouldn't give you everything you want

billy218 Frequent Visitor
Frequent Visitor

Re: Average of Sums

Hi Jthomson,

Thank you for taking the time to reply!

What do you mean "does not coincide at all"? The totalHours field is the field I am hoping to get aggregate data on, grouped by CRM Flag.


The tricky point is that I need the Sum of TotalHours by CRM Flag and TransactionNo, and then I need the average of those sums.


Perhaps it is obvious to you how this is done, but it is not to me, so if you could provide further instructions, that would be appreciated.


Helpful resources

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 30 members 769 guests
Please welcome our newest community members: