Forum Discussion

ss0102's avatar
ss0102
New Member
6 years ago
Solved

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

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:

 JanFeb
Bldg 1 New430402
Bldg 1 Old9491,824
Bldg 1 Total  (calculation)13792,226
Bldg 1 New  (calculation)31%18%

Bldg 1 Old % (calculation)

69%82%
Bldg 2 New100400
Bldg 2 Old9001600

Bldg 2 Total

(calculation)

10002000

Bldg2 New % (calculation)

10%20%

Bldg2 Old % (calculation)

90%80%
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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