Forum Discussion
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:
| 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.
@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-Last-Week/ba-p/1051123
2 Replies
- amitchandak
Super User
@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-Last-Week/ba-p/1051123 - Icey
Community Support
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.