Average of Sums

[Anonymous]

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

[jthomson]

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

[Anonymous]

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!