Forum Discussion
Cumulative % calculation
Hi
I am working on data which requires cumulative % in column.
Output required:
I am looking to add % week on week to get below output.
eg: In below table 2019 Actual for week 2 measure should show 33.49% which is addition of 14.30 + 19.19 (from table above) and so on
I have tried multiple ways but end up in creating % column which gives me cumulative % that adds up to 100% but not like above.
Can anyone help?
Regards
2 Replies
saukulkarni22 , You can use Week Number of week Rank, better it come from a separate date/week table
example measure
CALCULATE(AverageX(Values(Date[Week Rank] ), [Your Mesure]), FILTER(ALL('Date'), 'Date'[Week Rank]<=max('Date'[Week Rank])))
or
CALCULATE(AverageX(Values(Date[Week number] ), [Your Mesure]), FILTER(ALL('Date'), 'Date'[Week number]<=max('Date'[Week number])))
You can use allselected in place of all
CALCULATE(AverageX(Values(Date[Week Rank] ), [Your Mesure]), FILTER(ALLselected('Date'), 'Date'[Week Rank]<=max('Date'[Week Rank])))
CALCULATE(AverageX(Values(Date[Week number] ), [Your Mesure]), FILTER(ALLselected('Date'), 'Date'[Week number]<=max('Date'[Week number])))
Week rank column
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatPower BI — Week on Week and WTD
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
https://www.youtube.com/watch?v=pnAesWxYgJ8Hello,
Thank you for the reply. However I am not able to write week number measure/column in my table due to RANKX. I can't see RANKX in my power BI. Can you think of any reason?
Regards
