Forum Discussion
Display Sum
Hello Altruists,
A Table is given like below. "Week" will be increased or decreased randomly.
| Person-- | Week1-- | Week2-- | Week3-- | ... |
| Hoover-- | 2-- | 13-- | 30-- | ... |
| Franklin-- | 8-- | 3-- | 4-- | ... |
| Lincoln-- | 9-- | 9-- | 2-- | ... |
How to let Power BI to display a graph where each week would display sum of values of all previous weeks ?
Such as, Week 3 will display sum of values of Week1+Week2+Week3 , as table below.
Creating new columns would not be an option due to the fact that "week" row will be increased or decreased.
| Person-- | Week1-- | Week2+All previous week's value-- | Week3 +All previous week's value-- | ... |
| Hoover-- | 2-- | 15-- | 45-- | ... |
| Franklin-- | 8-- | 11-- | 15-- | ... |
| Lincoln-- | 9-- | 18-- | 20-- | ... |
Can someone help please?
Thank you in advance.
6 Replies
hello , sparvez
you need to unpivot your table from power query first.
then you need a cumulative measure :
try using the following :calculate (
sum( table[col] ,
all(dimdate) ,
dimdate[date] <= max(dimdate[date])
)or you can use window function :
Measure 11 =CALCULATE(SUM('Table (13)'[Value]),CALCULATETABLE(WINDOW(0,ABS,1,SUMMARIZE('Table (13)','Table (13)'[Index],'Table (13)'[Person],'Table (13)'[week nb]),ORDERBY('Table (13)'[week nb] , asc)),all('Table (13)'[week nb]),'Table (13)'[week nb] <= MAX('Table (13)'[week nb])))hope this is what you are looking for.
best regards
Hi, I get error, can u pls upload the power BI file ? thanks
