Forum Discussion
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 first row as (101,533,231/619,191,731)*100.
I am trying figure out a measure for it but so far without much success. Any help would be appreciated.
Like this?
Percentage = DIVIDE ( CALCULATE ( SUM ( Table1[Amount] ), Table1[Spending] = "MBE" ), CALCULATE ( SUM ( Table1[Amount] ), Table1[Spending] = "Expenditure" ) )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.
4 Replies
- AlexisOlson
Super User
Like this?
Percentage = DIVIDE ( CALCULATE ( SUM ( Table1[Amount] ), Table1[Spending] = "MBE" ), CALCULATE ( SUM ( Table1[Amount] ), Table1[Spending] = "Expenditure" ) )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
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.
