Calculate column totals and Averages in Table

[Anonymous]

Good day, I have data as per below example:

Date	Reason	Employee	Available
2020/08/25	LEAVE	11111	10
2020/08/25	LEAVE	22222	10
2020/08/25	LEAVE	22222	10
2020/08/25	LEAVE	11111	10
2020/08/25	OFF	33333	10
2020/08/26	OFF	11111	8
2020/08/26	OFF	22222	8
2020/08/26	OFF	11111	8
2020/08/26	OFF	22222	8
2020/08/26	LEAVE	33333	8
2020/08/26	LEAVE	44444	8
2020/08/27	LEAVE	55555	4
2020/08/27	LEAVE	66666	4
2020/08/27	OFF	22222	4

What I would like is the results as per below in a table with averages instead of Total line. 

Date	Available on Date	OFF	LEAVE	TOTAL	AVAILABILITY
2020/08/25	10	4	1	5	50%
2020/08/26	8	2	4	6	75%
2020/08/27	4	2	1	3	75%
Average	7,3	2,7	2,0	4,7	67%

Note I did not use a matrix, but if its possible with a matrix please let me know.

Thanks

The problem is the Average row and the availability rows. How can this be accomplished?

[amitchandak]

@Anonymous , Not sure on the logic of other columns.

The first one looks like

averageX(summarize(Table, Table[Date],Table[Available]),[Available])

or

averageX(summarize(Table, Table[Date],"_1",max(Table[Available])),[_1])

[Anonymous]

Thank you. averageX(summarize(Table, Table[Date],Table[Available]),[Available] works for calculating the availability. But how to do each of the other columns ensuring that the average is displayed in the Average line?

	Available on Date	OFF	LEAVE	TOTAL	AVAILABILITY
2020/08/25	10	4	1	5	50%
2020/08/26	8	2	4	6	75%
2020/08/27	4	2	1	3	75%
Average	7,3	2,7	2,0	4,7	67%

[Greg_Deckler]

@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also, this might help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150

[Anonymous]

@Greg_Deckler I have tried researching working with average columns but I'm not sure how to apply what you have suggested. My current formula looks like this:

LEAVE AVG = AVERAGEX('TABLE', CALCULATE(DISTINCTCOUNT('TABLE'[EMPLOYEE] ),FILTER('TABLE','TABLE'[REASON] = "LEAVE")))

The Average Row does not display the averages, instead it displays the Total

[V-pazhen-msft · Accepted solution]

@Anonymous 

This is the grand total line, it does not change by the averagex() function. To get average you can just divide by the distinctcount([date]). For example the LEAVE AVG:

LEAVE AVG = 
VAR leave= 
CALCULATE( DISTINCTCOUNT('TABLE'[EMPLOYEE] ),FILTER('TABLE','TABLE'[REASON] = "LEAVE") )
RETURN
IF(HASONEFILTER('Table'[Date]),leave, leave / DISTINCTCOUNT('Table'[Date]))

 or this measure should also work:

LEAVE AVG  = CALCULATE( DISTINCTCOUNT('TABLE'[EMPLOYEE] ),FILTER('TABLE','TABLE'[REASON] = "LEAVE") ) / DISTINCTCOUNT('Table'[Date])

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.