Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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...
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.
@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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |