Forum Discussion

tonyclifton's avatar
tonyclifton
Icon for Helper III rankHelper III
6 years ago
Solved

Percentage Calculation of Matrix Total Row

Hello community,

I am trying to get the percentage calculation for the total row of my calculated column "KPI Dev %" right.
The below calculation is correct for each row however for the total row I want to divide the total of KPI DEV by KPI_act.

Background: I have to compare each calendar week to the previous one and calculate the deviation between two weeks.

 

KPI DEV = 
VAR PreviousRow =
    TOPN (
        1,
        FILTER (
            'Table',
              'Table'[CalendarWeek] < EARLIER ('Table'[CalendarWeek]) &&
                 'Table'[YearMonth] = EARLIER ( 'Table'[YearMonth])
                 && 'Table'[Company] = EARLIER ( 'Table'[Company])
        ),
        [CalendarWeek], DESC
    )
VAR PreviousValue =
    MINX ( PreviousRow, [KPI_act] )
RETURN 'Table'[KPI_act] - PreviousValue

 

 

"KPI DEV %" = same as KPI DEV but different return: RETURN CALCULATE(DIVIDE( SUM('Table'[KPI DEV]), PreviousValue))


So in below example for week 38 instead of Sum or Average function over those three rows this is my desired calculation:
Total of (KPI DEV / KPI_act) => 362/22563 = 1,604%

Do you have an idea how this can be achieved?

Here's my raw table data:

YearMonthCompanyCalendarWeekKPI_act
01.09.2020 00:00:00Company A3711467
01.09.2020 00:00:00Company A3811697
01.09.2020 00:00:00Company B379607
01.09.2020 00:00:00Company B389742
01.10.2020 00:00:00Company A3711702
01.10.2020 00:00:00Company A3811823
01.10.2020 00:00:00Company B379528
01.10.2020 00:00:00Company B389793
01.09.2020 00:00:00Company C371127
01.09.2020 00:00:00Company C381124
01.10.2020 00:00:00Company C37975
01.10.2020 00:00:00Company C38874


Thank you.

 

2 Replies