Calculate % of the average per location based on the location area

[Anonymous]

I am trying to do the below highlighted calculations and wasn’t quite sure how use DAX to do the calculations.

I want for pull the area average per each location (i.e per each row) suing DAX and then to get the turnover % by dividing column D by column E  which is easier but my challenge is to pull each area average against each location.

I hsve also included a sample data set.

Any help is greatly appreciated.

Location	Area	Game Name	Turnover Per Machine	Avg Turnover By Area	Trunover %
AA01	Regular	Game A	$7,278	$8,581.79	85%
AA02	Regular	Game B	$8,440	$8,581.79	98%
AA03	Regular	Game A	$6,689	$8,581.79	78%
AA04	Regular	Game C	$8,946	$8,581.79	104%
AA05	Regular	Game D	$10,463	$8,581.79	122%
AA06	Regular	Game B	$9,889	$8,581.79	115%
AA07	Regular	Game B	$7,565	$8,581.79	88%
AA08	Regular	Game C	$9,935	$8,581.79	116%
AA09	Regular	Game A	$6,556	$8,581.79	76%
AA10	Regular	Game C	$10,223	$8,581.79	119%
AA11	Regular	Game C	$10,191	$8,581.79	119%
AA12	Regular	Game D	$10,909	$8,581.79	127%
AA13	Regular	Game B	$7,713	$8,581.79	90%
AA14	Regular	Game B	$5,348	$8,581.79	62%
VV01	Premium	Game B	$7,279	$8,008.56	91%
VV02	Premium	Game D	$10,603	$8,008.56	132%
VV03	Premium	Game D	$5,913	$8,008.56	74%
VV04	Premium	Game D	$9,171	$8,008.56	115%
VV05	Premium	Game A	$5,657	$8,008.56	71%
VV06	Premium	Game D	$7,249	$8,008.56	91%
VV07	Premium	Game C	$8,329	$8,008.56	104%
VV08	Premium	Game B	$9,108	$8,008.56	114%
VV09	Premium	Game C	$8,768	$8,008.56	109%
XX01	VIP	Game B	$9,887	$11,192.69	88%
XX02	VIP	Game C	$7,636	$11,192.69	68%
XX03	VIP	Game A	$8,385	$11,192.69	75%
XX04	VIP	Game D	$11,165	$11,192.69	100%
XX05	VIP	Game A	$17,507	$11,192.69	156%
XX06	VIP	Game D	$17,800	$11,192.69	159%
XX07	VIP	Game C	$7,012	$11,192.69	63%
XX08	VIP	Game D	$12,170	$11,192.69	109%
XX09	VIP	Game A	$9,794	$11,192.69	88%
XX10	VIP	Game A	$7,901	$11,192.69	71%
XX11	VIP	Game A	$11,759	$11,192.69	105%
XX12	VIP	Game C	$16,618	$11,192.69	148%
XX13	VIP	Game D	$7,871	$11,192.69	70%

[Ashish_Mathur]

Hi,

If Turnover per machine is a column already present in your dataset, then try this calculated column formula

=CALCULATE(AVERAGE(Data[Turnover Per Machine]),FILTER(Data,Data[Area]=EARLIER(Data[Area])))

Hope this helps.

[Anonymous]

@Ashish_Mathur  

no its not in the data set and it needs to calculate using another DAX.

The orginal data set has a date column as well and using that fromula i am getting  different answer.

Is there a way I can share the data set with you ?

Thanks,

Deevs

[Ashish_Mathur]

Hi,

Share the download link of the file.

[Anonymous]

@Ashish_Mathur 

How do i do that as there is no option to upload a excel or a pbix file here and I don't have one drive either. Can I send you via linkedin ?

[Ashish_Mathur]

Hi,

Try Google Drive.

[amitchandak]

@Anonymous , Try one of the two

// this should consider filters
divide(sum(Table[Turnover Per Machine]),calculate(sum(Table[Turnover Per Machine]), filter(allselected([Table]),Table[Area] =max(Table[Area]))))

divide(sum(Table[Turnover Per Machine]),calculate(sum(Table[Turnover Per Machine]), allexcept([Table],Table[Area] )))

[stevedep · Accepted solution]

Hi,

__AvgTurnOverbyArea = CALCULATE(AVERAGE('Table'[Turnover Per Machine]),ALLEXCEPT('Table','Table'[Area]))

___TurnOverPerc = SUM('Table'[Turnover Per Machine])/[__AvgTurnOverbyArea]

Link to file. 

Please mark as solution if so. Thumbs up for the effort are appreciated.

Kind regards, 
Steve.  

[Anonymous]

@stevedep 

Can I send you the data set I am working on as the “turnover per machine” column on the data I uploaded was also need to be calculated. The way I calculate the Turnover per Machine is by dividing Turnover by location count for a given period.

Your formula working perfectly fine for the data set I uploaded and now I am trying to figure how to do the turnover per mac calculation on the same DAX formula.

Any chance I can directly message you the data set I am working on as there is no option to upload a large data set here in an excel format.