topic Re: Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total in DAX Commands and Tips

Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total

ChinrajP — Mon, 08 Jul 2024 10:49:15 GMT

Dear All,

I am working on a project for P&L reports in PBI. We want to get to know the employee contribution towards the projects worked. In that, I am facing a problem in computing the Grand Total value in a Matrix table. The visual shows us the sum of the sub-total in grand total. But we need to show the average of the sub-total in grand total. Please guide me, on how to achieve this. Attached is the screenshot of the current and to-be methods.

Sample File: Sample.pbix

Thanks,

ChinrajP

Re: Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total

BeaBF — Mon, 08 Jul 2024 09:09:39 GMT

ChinrajP Hi! Can you attach sample data and the measures in power bi?

BBF

Re: Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total

ChinrajP — Mon, 08 Jul 2024 12:32:24 GMT

Sample File: https://drive.google.com/file/d/1N_2PNiY4_pvqE8VKLf3o2J2uz7l_7Vy-/view?usp=drive_link

Re: Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total

BeaBF — Mon, 08 Jul 2024 12:15:54 GMT

ChinrajP

Re: Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total

ChinrajP — Mon, 08 Jul 2024 12:32:52 GMT

https://drive.google.com/file/d/1N_2PNiY4_pvqE8VKLf3o2J2uz7l_7Vy-/view?usp=drive_link

Revised the path. Please help me. Thanks

Re: Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total

BeaBF — Mon, 08 Jul 2024 12:38:46 GMT

ChinrajP please accept my request to access.

Re: Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total

ChinrajP — Mon, 08 Jul 2024 13:05:10 GMT

Accepted the request. Please do the need if possible. Thanks

Re: Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total

BeaBF — Mon, 08 Jul 2024 13:16:52 GMT

ChinrajP Here the measure:

Measure_BBF =

IF (

ISINSCOPE ( Sheet1[Project Name] ) ,

SUM ( Sheet1[Effort Contribution]),

SUMX(Sheet1, (Sheet1[Effort Contribution])) / DISTINCTCOUNT(Sheet1[Project Name])

)

Use the measure in the matrix, instead of the default sum of the field Effort Contribution.

BBF

Re: Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total

BeaBF — Mon, 08 Jul 2024 13:59:38 GMT

ChinrajP If it works, please accept my answer as solution, thanks

Re: Matrix Visual: Required Average of Sub-Total in Grand Total instead of SUM of Sub-Total

ChinrajP — Mon, 08 Jul 2024 16:39:54 GMT

It works fine. Thank you very much for the help.