Percentage Calculation of Matrix Total Row

[tonyclifton]

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:

YearMonth	Company	CalendarWeek	KPI_act
01.09.2020 00:00:00	Company A	37	11467
01.09.2020 00:00:00	Company A	38	11697
01.09.2020 00:00:00	Company B	37	9607
01.09.2020 00:00:00	Company B	38	9742
01.10.2020 00:00:00	Company A	37	11702
01.10.2020 00:00:00	Company A	38	11823
01.10.2020 00:00:00	Company B	37	9528
01.10.2020 00:00:00	Company B	38	9793
01.09.2020 00:00:00	Company C	37	1127
01.09.2020 00:00:00	Company C	38	1124
01.10.2020 00:00:00	Company C	37	975
01.10.2020 00:00:00	Company C	38	874

Thank you.

[Icey]

Hi @tonyclifton ,

Please refer to this post: Power BI: Totals Incorrect.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@tonyclifton, Based on what I have.

For Week-to-Week Better to Create a Week Range on the Start Date of the Week at Week of the Year

Year Week to [Year]*100 + [Week]

Column

Rango de la semana - RANKX (all('Date'),'Date'[Week Start date],,ASC,Dense)

Medida
Esta semana: CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]-max('Date'[Week Rank])))
Last Week ? CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]-max('Date'[Week Rank])-1))

refer to the blog

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...