Search code examples
matrixpowerbireportdaxdashboard

Calculated SubTotal By Year, measure not updating with Slicer in Power BI

i have the below matrix where Market Company Name is in Matrix Rows as well as a Slicer. i need to calculate the total per year irrespective market company name in order to calculate "% Distribution" which is equal to Premium per year per Market Company Name divide by Total Premium by Year. enter image description here

i tried to create the below test measure:

test Measure = CALCULATE(SUM('Production Reinsurer'[Gross Written Premium]),
                    ALLEXCEPT('Production Reinsurer','Production Reinsurer'[Year], 
                                'Production Reinsurer'[Entity Name],
                                'Production Reinsurer'[Status],
                                'Production Reinsurer'[Cover Type],
                                'Production Reinsurer'[LOB]))

it worked perfectly except if i select a market company name from the slicer, all figures will be wrong.

And if i add Market Company Name to the formula it will calculate the Sub total per year per company which is wrong:

test Measure = CALCULATE(SUM('Production Reinsurer'[Gross Written Premium]),
                    ALLEXCEPT('Production Reinsurer','Production Reinsurer'[Year], 
                                'Production Reinsurer'[Entity Name],
                                'Production Reinsurer'[Status],
                                'Production Reinsurer'[Cover Type],
                                'Production Reinsurer'[LOB],
                                'Production Reinsurer'[Market Company Name]))

enter image description here

appreciate your assistance

Georges

Solution

  • I was able to do it using the below measure:

    test Measure = CALCULATE(SUM('Production Reinsurer'[Gross Written Premium]),
                    ALLEXCEPT('Production Reinsurer','Production Reinsurer'[Year], 
                                'Production Reinsurer'[Entity Name],
                                'Production Reinsurer'[Status],
                                'Production Reinsurer'[Cover Type],
                                'Production Reinsurer'[LOB],
                                'Production Reinsurer'[Market Company Name]),
                                ALLSELECTED('Production Reinsurer'[Market Company Name]))

    regards, Georges