cancel
Showing results for
Did you mean:
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_Flag Wave_Trans_NO DecisionDateTime Total Hours Last Step? No 365819 2019-06-27 14:53 137.91 0 No 365819 2019-07-03 8:47 0.86 0 No 365819 2019-07-03 9:39 0.02 0 No 365819 2019-07-03 9:40 0.46 0 No 365819 2019-07-03 10:08 0.02 0 No 365819 2019-07-03 10:09 2.98 0 No 365819 2019-07-03 13:08 0.27 0 Yes 365819 2019-07-03 13:25 337.35 0 Yes 365819 2019-07-17 14:46 -1056.33 1 No 372545 2019-06-03 14:26 0.05 0 No 372545 2019-06-03 14:29 17.59 0 No 372545 2019-06-04 8:05 2.71 0 No 372545 2019-06-04 10:47 2.15 0 Yes 372545 2019-06-04 12:56 480.90 0 Yes 372545 2019-06-24 13:50 0.15 0 Yes 372545 2019-06-24 14:00 -503.56 1

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' No 140.12 120.2 Yes 35.3 16.4

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!!

2 REPLIES 2
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

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.

Thanks!

Announcements

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

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

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 30 members 769 guests
Recent signins: