Forum Discussion

joshig's avatar
joshig
Frequent Visitor
4 years ago
Solved

Calculate Percentage based on Matrix Columns

I am working on Matrix visual and I have to calculate percentage based on the Column data. The data is represented below and I need a new column percentage that needs to be calculated for e.g for the...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Like this?

    Percentage =
DIVIDE (
    CALCULATE ( SUM ( Table1[Amount] ), Table1[Spending] = "MBE" ),
    CALCULATE ( SUM ( Table1[Amount] ), Table1[Spending] = "Expenditure" )
)
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I'd recommend taking SPENDING off of the matrix columns and defining separate measures for MBE and Expenditure then putting all three measures in the visual. You can use the numerator and denominator of the measure I gave before to define these new measures.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

Like this?

Percentage =
DIVIDE (
    CALCULATE ( SUM ( Table1[Amount] ), Table1[Spending] = "MBE" ),
    CALCULATE ( SUM ( Table1[Amount] ), Table1[Spending] = "Expenditure" )
)
  • joshig's avatar
    joshig
    Frequent Visitor
    4 years ago

    Thanks Alexis that works like a charm!! And now i realize the true power of Measures. Do you know how do I add it to the matrix so that it just shows up once as a column after 'MBE'  in my visual. If i drop it under Values section the matrix makes it appear in three separate columns after each column that i have in my image above.

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      I'd recommend taking SPENDING off of the matrix columns and defining separate measures for MBE and Expenditure then putting all three measures in the visual. You can use the numerator and denominator of the measure I gave before to define these new measures.

      • joshig's avatar
        joshig
        Frequent Visitor
        4 years ago

        Thanks again and really appreciate providing me a new perspective. It helped me a lot since i have just started my journey wth PBI.