Forum Discussion

js124's avatar
js124
Frequent Visitor
6 years ago

Matrix table question

I've created a matrix table showing Media Division % for 2018 and 2019:    Instead of a Totals column, which is adding 2018 and 2019 percentages together, I want to have a column on the right...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi

 

I would make a measure like the one below (you should probably make something more dynamic on the year filtering to make it apply in 2020):

value measure = 

    IF(
        ISFILTERED('Table'[Year]);
        SUM('Table'[Value]) / CALCULATE( SUM( 'Table'[Value]); ALLEXCEPT( 'Table'; 'Table'[Year]));
        CALCULATE( SUM('Table'[Value]); 'Table'[Year] = 2019) - CALCULATE( SUM( 'Table'[Value]); 'Table'[Year] = 2018)
    )

If this works then please mark it as the accepted solution.

js124's avatar
js124
Frequent Visitor
6 years ago

Thanks Anonymous . While I can understand the logic, unfortunately I'm getting errors when trying to use the measure you suggested:

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    If you have an american setup then you should probably change the semicolons to commas.

     

    Is your datamodel just one table or do you have multiple tables, i.e. dimDate?

     

    IF(
        ISFILTERED('Table'[Year]),
        SUM('Table'[Value]) / CALCULATE( SUM( 'Table'[Value]), ALLEXCEPT( 'Table', 'Table'[Year])),
        CALCULATE( SUM('Table'[Value]), 'Table'[Year] = 2019) - CALCULATE( SUM( 'Table'[Value]), 'Table'[Year] = 2018)
    )