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

[ChinrajP]

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

[BeaBF · Accepted solution]

@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

[ChinrajP]

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

[BeaBF · Accepted solution]

@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

[ChinrajP · Accepted solution]

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

[BeaBF]

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

[BeaBF]

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

BBF

[ChinrajP]

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

[BeaBF]

@ChinrajP 

[ChinrajP]

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

Revised the path. Please help me. Thanks

[BeaBF]

@ChinrajP please accept my request to access.