Calculating the % for the Row subtotal in Table/matrix visual

[ss0102]

Hi Friends,

Looking for help and hoping someone has already navigated this water and can help.  I had a tabular visual in Excel which looks like below.   My Data table has Bldg and New/Old attached to Patient ID and so I can aggregate them, and in my Excel table, just divide the cells like New/Total to create the % subtotals. 

In Power BI, I have a Matrix visual where I have the Bldg and New/Old as rows, month as a column and Values as Count (patient ID) so I am able to create the rows for New, Old and Total.  However, I am not able to create the % anymore as not sure how to divide the New/Total. 

Do I need to create a measure or calculated column?  How do I go about it?  Thanks a lot for your help.

EXCEL VIEW:

	Jan	Feb
Bldg 1 New	430	402
Bldg 1 Old	949	1,824
Bldg 1 Total  (calculation)	1379	2,226
Bldg 1 New  (calculation)	31%	18%
Bldg 1 Old % (calculation)	69%	82%
Bldg 2 New	100	400
Bldg 2 Old	900	1600
Bldg 2 Total (calculation)	1000	2000
Bldg2 New % (calculation)	10%	20%
Bldg2 Old % (calculation)	90%	80%

[v-jayw-msft · Accepted solution]

Hi @ss0102 ,

Column or Measure is al

Here's the formula for calculated column.

jan%_c = CALCULATE(SUM('Table'[Jan]),ALLEXCEPT('Table','Table'[Bldg],'Table'[New/Old]))/CALCULATE(SUM('Table'[Jan]),ALLEXCEPT('Table','Table'[Bldg]))

feb%_c = CALCULATE(SUM('Table'[Feb]),ALLEXCEPT('Table','Table'[Bldg],'Table'[New/Old]))/CALCULATE(SUM('Table'[Feb]),ALLEXCEPT('Table','Table'[Bldg]))

Best Regards,

Jay

[sangeetaraman]

This is so helpful! Thank you

[AiolosZhao]

Hi @ss0102 ,

Please try the below measures:

JAN % = DIVIDE(CALCULATE(SUM('Table (2)'[JAN])),
                CALCULATE(SUM('Table (2)'[JAN]),ALLEXCEPT('Table (2)','Table (2)'[BLDG])),0)

FEB % = DIVIDE(CALCULATE(SUM('Table (2)'[FEB])),
                CALCULATE(SUM('Table (2)'[FEB]),ALLEXCEPT('Table (2)','Table (2)'[BLDG])),0)

Aiolos Zhao