how to average revenue at a higher level

[dfox09]

I am writing this in the hopes that the Aces of the community may have encountered something like this before. I have a table that has producer, client and revenue (among other fields). The revenue is at the client level. Each producer will have multiple clients. It's no problem summing up the revenue to the producer level but what I can't figure out is how to average the revenue at the producer level. When I use a formula like this: 

Producer_Rev = Calculate(

AVERAGE('TBL_Actuals'[Producer Revenue]),

ADDCOLUMNS('TBL_Actuals',"Producer",'TBL_Actuals'[ProducerName])), it averages the revenue for all of the clients within a producer. What I would like to see would be an average of the producer revenue across x number of periods (in my case,months). 

Thank you!

Don

[lbendlin]

Without seeing your sampe data I would think that AverageX() is what you are looking for.

[dfox09]

Here is a sample of the table. "BeginDate' is from a DimAccounting table that is joined by AcctPD. I included multiple months to see if there is a way to show how to average across months. My full data set goes back to 04/01/2019. Here is a formula that I was not able to get to work: 

Foward Forecast =

CALCULATE(a

AVERAGEX(

'Measures Table','Measures Table'[Producer_Rev]),

DATESBETWEEN('DimAccountingPeriod'[BeginDate],

DATEADD(DimAccountingPeriod[Today],-11,MONTH),

DATE(2020, 04,30)))

"Producer_Rev" is 

Producer_Rev = Calculate(

AVERAGE('TBL_Actuals'[Producer Revenue]),

ADDCOLUMNS('TBL_Actuals',"Producer",'TBL_Actuals'[ProducerName]))

BeginDate	AcctPD	ProducerName	Parent Client	Producer Revenue
5/1/2020	202005	Mickey Mantle	Barbara Bufkin & Michael Wybar	($6.44)
5/1/2020	202005	Mickey Mantle	Doug Dirks	$78.07
5/1/2020	202005	Mickey Mantle	Douglas & Sandra Johnson	$71.10
5/1/2020	202005	Mickey Mantle	Douglas and Virginia Ware	$161.84
5/1/2020	202005	Mickey Mantle	Felix Augusto & Elan Sasso	$1,829.53
5/1/2020	202005	Mickey Mantle	George & Joan Lange	$411.38
5/1/2020	202005	Mickey Mantle	George and Martha Richards	$90.31
5/1/2020	202005	Mickey Mantle	James Gearen	($5.30)
5/1/2020	202005	Mickey Mantle	Jeff & Erin Prendergast	$839.06
5/1/2020	202005	Mickey Mantle	Kathie and Wayne Volland	$699.16
5/1/2020	202005	Mickey Mantle	Kathy Richter & Lisa Gladstone	$77.41
5/1/2020	202005	Mickey Mantle	Kenneth & Chaumarie Chastain	$1,044.01
5/1/2020	202005	Mickey Mantle	Kyle & Kate Watson	($482.38)
5/1/2020	202005	Mickey Mantle	Lawrence & Caroline Heard	$15.93
5/1/2020	202005	Mickey Mantle	Lois B. Pope	$2,703.22
5/1/2020	202005	Mickey Mantle	Michael McGowan	$682.45
5/1/2020	202005	Mickey Mantle	Richard & Helen McCourt	$1,639.45
5/1/2020	202005	Mickey Mantle	Robert and Melanie Stanek	($73.88)
5/1/2020	202005	Mickey Mantle	Rodney & Lisa Ragland	$582.65
5/1/2020	202005	Mickey Mantle	Samuel & Sarah Cooper	$84.96
5/1/2020	202005	Mickey Mantle	Scott & Chellye Tanberg	$71.10
5/1/2020	202005	Mickey Mantle	Scott Ellington	$1,433.52
5/1/2020	202005	Mickey Mantle	Steven and Debra Rowley	$4,801.20
5/1/2020	202005	JJ Abrams	Ascend Athol RE LLC	$7,403.60
5/1/2020	202005	JJ Abrams	Brookhaven Science Associates	$6,931.83
5/1/2020	202005	JJ Abrams	Marshall, Dennehey, Warner, Coleman & Goggin	$5,808.10
5/1/2020	202005	JJ Abrams	Verus Claims Services, LLC	$86.10
5/1/2020	202005	JJ Abrams	Weill Cornell Medicine	$45,371.27
5/1/2020	202005	JJ Abrams	Yeshiva University	$5,382.54
5/1/2020	202005	Bailey,George	Aura	$16,618.92
5/1/2020	202005	Bailey,George	Billy Casper Golf, LLC	$18,123.60
5/1/2020	202005	Bailey,George	Campaign Monitor	$5,240.46
5/1/2020	202005	Bailey,George	Choice Hotels International, Inc.-BEN	$8,905.88
5/1/2020	202005	Bailey,George	Coastal Sunbelt Produce	$1,871.22
5/1/2020	202005	Bailey,George	Communication Technologies Inc	$269.28
5/1/2020	202005	Bailey,George	Crews Enterprises, LLC	$8,434.42
5/1/2020	202005	Bailey,George	CSSI, Inc.	$6,181.29
5/1/2020	202005	Bailey,George	Emergent Biosolutions Inc.	$15,271.41
5/1/2020	202005	Bailey,George	EVAPCO	$10,940.64
5/1/2020	202005	Bailey,George	Five Guys Holdings, Inc.	$2,891.97
5/1/2020	202005	Bailey,George	Lockton - Dunning	$666.75
5/1/2020	202005	Bailey,George	Lockton Atlanta (Hartford) - LOCAT15	$13,240.75
5/1/2020	202005	Bailey,George	Lockton Dunning (Hartford) - LOCDU21	$1,409.59
5/1/2020	202005	Bailey,George	Lockton KC - LOCCO14	$5,173.12
5/1/2020	202005	Bailey,George	MCI, USA	$7,340.25
5/1/2020	202005	Bailey,George	MPR Associates	$1,445.13
5/1/2020	202005	Bailey,George	Nash Holdings LLC	$7,604.98
5/1/2020	202005	Bailey,George	Phillips Corporation	$15,438.58
5/1/2020	202005	Bailey,George	REGENXBIO, Inc.	$1,494.94
5/1/2020	202005	Bailey,George	Salient CRGT	($196.34)
5/1/2020	202005	Bailey,George	TechSource	$4,225.51
5/1/2020	202005	Frank Capra	Carlyle Realty Partners LP	$462,250.11
5/1/2020	202005	Frank Capra	Duke Realty Corporation	$652.95
5/1/2020	202005	Frank Capra	GMH Associates, Inc	$7,872.25
5/1/2020	202005	Frank Capra	Mad Happy, LLC	$0.00
5/1/2020	202005	Frank Capra	Merion Realty Management, LLC	$3,668.18
5/1/2020	202005	Frank Capra	MMA Capital Holdings, Inc.	$27,368.25
5/1/2020	202005	Frank Capra	Nahla Capital	$6,250.00
5/1/2020	202005	Frank Capra	Pulse EFT Association, LP	$0.00
5/1/2020	202005	Frank Capra	Sunrise Senior Living, LLC	$29.21
5/1/2020	202005	Frank Capra	TruNorth Mechanical, LLC	$321.15
5/1/2020	202005	Frank Capra	Woodfield Investment Company, LLC	$7,713.40

[lbendlin]

See if this helps for the average producer revenue.

Producer_Rev = DIVIDE(sum(TBL_Actuals[Producer Revenue]),DISTINCTCOUNT(TBL_Actuals[ProducerName]))

[dfox09]

Thank you for your reply. When I plug that formula in, I get the same amount as if I was using another formula. Here is the other formula.  

Production = CALCULATE(sum(TBL_Actuals[Producer Revenue])) which yeilds the same result. Where it gets hazy for me is trying to do an average of that revenue.

Here is the formula that I tried but I can't get it to work - 

Foward Forecast =

CALCULATE(

AVERAGEX(

'Measures Table','Measures Table'[Producer_Rev]),

DATESBETWEEN('DimAccountingPeriod'[BeginDate],

DATEADD(DimAccountingPeriod[Today],-11,MONTH),

DATE(2020, 04,30)))

 . When I put it in place I get the following message: 

MdxScript(Model) (127, 13) Calculation error in measure 'Measures Table'[Foward Forecast]: Table 'Measures Table' cannot be used in computations because it does not have any columns.

Any suggestions?

[lbendlin]

The average across producers is a single number, in the totals. Are you expecting it to display next to each producer? If so, why?

[dfox09]

Good question. What I'm trying to show would be a forward looking forecast based on the average of the producer revenue from the month (of the prior fiscal year) to the last month of the prior fiscal year, in my case, April, 2020. Our Fiscal year goes from May to April. Hence that crazy looking formula that i pasted in.  This might show something like this;

I can do that formula in Excel but can't do it in dax or power query. I could use some help in that.

Don

[lbendlin]

That is a rather different ask from the original question. See if this thread can be of help

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rolling-12-Month-Average-result-used-to-Forec...

[dfox09]

I read the post and like what you came up with for the rolling 12 month average. I was able to modify it, to a point, for my purposes. Wondering if you might be able to help me figure out how to calculate an average that averages monthly revenue(like in your other post) from the month that is the month after the month, that a producer is being forecasted for, up to the last month of the prior Fiscal Year. For example if Wayne is a Producer and we are trying to forecast August, 2020 then we would average the monthly revenue from September, 2019 through April, 2020 (The last month of the prior fiscal year). If we were to forecast September, it would be October, 2019 through April, 2020.

Much appreciated,

Don

[lbendlin]

Yes, all you need to do is change the EDATE() offsets.

'FactTable'[Day]>=EDATE(m,-12) && 'FactTable'[Day]<EDATE(m,0)),

I changed it slightly to add the edate for the second comparison. You would need to change the -12 and 0 accordingly.

[dfox09]

Thank you for that suggestion. I will try it. In the meantime, here is what I did:

Foward Forecast =

var m = SELECTEDVALUE(DimAccountingPeriod[BeginDate])

var t = (m>TODAY())

var a = CALCULATE(SUM(TBL_Summarized_Actuals[Revenue]),DimAccountingPeriod[BeginDate]>EDATE(m,-12),DimAccountingPeriod[BeginDate]<=DATE(2020,04,30))

Return a/12

I hardcoded 4/30/2020 since I knew that, that would always be the end date of the particular forecasting period.

I am real close to, but still not matching what I have in Excel. For example;

Name                    ID         Month        Revenue      Budget           Forecast

Bailey,George	27179	6/1/2019	$            126,293	$            176,295	$             -	lbendlin
Bailey,George	27179	7/1/2019	$            148,910	$            136,995	$             -
Bailey,George	27179	8/1/2019	$            133,568	$            158,566	$             -
Bailey,George	27179	9/1/2019	$            200,510	$            156,340	$             -
Bailey,George	27179	10/1/2019	$            306,490	$            152,122	$             -
Bailey,George	27179	11/1/2019	$            153,296	$            146,923	$             -
Bailey,George	27179	12/1/2019	$            202,763	$            148,716	$             -
Bailey,George	27179	1/1/2020	$            191,809	$            139,800	$             -
Bailey,George	27179	2/1/2020	$            146,127	$            143,700	$             -
Bailey,George	27179	3/1/2020	$            222,464	$            143,700	$             -
Bailey,George	27179	4/1/2020	$            207,484	$            149,512	$             -
Bailey,George	27179	5/1/2020	$            152,592	$            179,863	$             -
Bailey,George	27179	6/1/2020	$            192,613	$            179,863	$  191,342	$  188,219
Bailey,George	27179	7/1/2020	$              16,145	$            185,863	$  196,057	$  184,962
Bailey,George	27179	8/1/2020		$            179,863	$  203,868	$  173,831
Bailey,George	27179	9/1/2020		$            179,863	$  204,348	$  157,122
Bailey,George	27179	10/1/2020		$            192,363	$  187,324	$  131,581
Bailey,George	27179	11/1/2020		$            194,863	$  194,129	$  118,806
Bailey,George	27179	12/1/2020		$            194,863	$  191,971	$  101,910

Your numbers are closer to the Excel forecast than what I tried. I didn't quite follow what you meant by "changing the -12 and 0". Is there a way to set this up relative to the month being forecasted?

[lbendlin]

yes, that's what EDATE() does - it allows you to shift the month window around and change its size according to your requirements.

Note: the smaller the window the less reliable the forecast will be.

[dfox09]

I think I am getting real close. Here is the modified formula:

Forward Forecast4 =

var m = SELECTEDVALUE(DimAccountingPeriod[BeginDate])

var t = (m>TODAY())

var i = DATEDIFF(m,Date(2020,04,30),MONTH)

var a = CALCULATE(SUM(TBL_Summarized_Actuals[Revenue]),DimAccountingPeriod[BeginDate]>EDATE(m,-i),DimAccountingPeriod[BeginDate]<=EDATE(m,0))

Return a/i

I think that the selected date will have to be the current month - 11 (months) and then, the calculation would be the average of the number of months between that date, whatever month that is, and 4/30/2020. I just don't know how to build that in to the formula.

[dfox09]

Can you tell me why this formula doesn't work (besides what Power BI is saying) and how to correct it?

Forward Forecast5 =

var m = SELECTEDVALUE(DimAccountingPeriod[BeginDate])

var t = EDATE(m,-12)

var i = DATEDIFF(t,Date(2020,04,30),MONTH)

var a = CALCULATE(SUM(TBL_Summarized_Actuals[Revenue]),

DATESBETWEEN('DimAccountingPeriod','DimAccountingPeriod'[BeginDate] = t,'DimAccountingPeriod'[BeginDate] = m))

Return a/i

Power BI Error - DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument.

[lbendlin]

var a = CALCULATE(SUM(TBL_Summarized_Actuals[Revenue]),

DATESBETWEEN('DimAccountingPeriod', t, m))

DATESBETWEEN needs date values, not comparisons.

[dfox09]

I had tried that solution before and PBI threw the error, "DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument."

For some reason, it doesn't accept a variable as a date.

Here is the whole formula:

Forward Forecast5 =

var m = SELECTEDVALUE(DimAccountingPeriod[BeginDate])

var t = EDATE(m,-12)

var i = DATEDIFF(t,Date(2020,04,30),MONTH)

var a = CALCULATE(SUM(TBL_Summarized_Actuals[Revenue]),

DATESBETWEEN('DimAccountingPeriod', t, m))

Return a/i

[lbendlin]

Doesn't sound like 'DimAccountingPeriod' is your Dates table ? Check the definition of DATESBETWEEN()

[dfox09]

so I went back and looked and realized that DimAccounting is not a tru date table as there were gaps in BeginDate. I brought in a real date table, DimDate and started using that but I got a real interesting result when I used it.

It gave me slightly inflated forecasted revenue numbers through the current month (7/1/2020) but showed "infinity" everything forward.

Here is the formula:

Forward Forecast5 =

var m = SELECTEDVALUE(DimDate[StartOfMonthDate])

var t = EDATE(m,-12)

var i = DATEDIFF(t,Date(2020,04,30),MONTH)

var a = CALCULATE(SUM(TBL_Summarized_Actuals[Revenue]),

DATESBETWEEN('DimDate'[Date],t,m))

Return a/i

See anything strange?

[dfox09]

Actually it is my Date table. BeginDate is datatype Date. is there something that I am not seeing?

As always, appreciate the help!