cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Carlo_V
Regular Visitor

How to show the Sum of the total column in the Matrix Table

Hi PBI Community,

 

I created a measure to divide one table column from another table column. I used this code:

 

Coach Requirement =
var headcount = CALCULATE(SUM(Forecast[Forecast]))
var cratio = CALCULATE(SUM(Ratio[Coach Ratio]),FILTER(Forecast,Forecast[Account with LOB]=RELATED(Ratio[Account with LOB])))
Return DIVIDE(headcount,cratio)

 

My problem is when I used this in the matrix table, it's also dividing the total (I think). Is there a way to change the Total showing in the Matrix to sum just like when we use Pivot in Excel?

 

Capture.JPG

 

Thanks so much for the assistance. 😊

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @Carlo_V ,

 

Take a look at ISINSCOPE() function.

https://docs.microsoft.com/fr-fr/dax/isinscope-function-dax 

You could create a new measure like below:

Measure = IF(ISINSCOPE([column]),[Coach Requiremen],sumx('table',[Coach Requiremen]))

 

Best Regards,

Jay

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

View solution in original post

1 REPLY 1
v-jayw-msft
Community Support
Community Support

Hi @Carlo_V ,

 

Take a look at ISINSCOPE() function.

https://docs.microsoft.com/fr-fr/dax/isinscope-function-dax 

You could create a new measure like below:

Measure = IF(ISINSCOPE([column]),[Coach Requiremen],sumx('table',[Coach Requiremen]))

 

Best Regards,

Jay

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors