Forum Discussion
Running Total at drill down doesnt work
Hi,
I have a bar + line chart that shows Year and Quarter at one level and Week at drill down level.
I want that the running total line that shows right values at the year quarter level shold also show the right values at week level.
How can i make sure i get the right values?
Running total calculation
Profit running total in Year Quarter =
CALCULATE(
SUM('Table'[Profit]),
FILTER(
ALLSELECTED('Table'[Year Quarter]),
ISONORAFTER('Table'[Year Quarter], MAX('Table'[Year Quarter]), DESC)
)
)
Data used above:
| Year Quarter | Weak | Profit |
| 2020Q1 | WK1 | 14 |
| 2020Q1 | WK2 | 15 |
| 2020Q1 | WK3 | 10 |
| 2020Q1 | WK4 | 15 |
| 2020Q2 | WK1 | 12 |
| 2020Q2 | WK2 | 12 |
| 2020Q2 | WK3 | 18 |
| 2020Q2 | WK4 | 11 |
| 2020Q3 | WK1 | 12 |
| 2020Q3 | WK2 | 10 |
| 2020Q3 | WK3 | 10 |
| 2020Q3 | WK4 | 19 |
| 2020Q4 | WK1 | 11 |
| 2020Q4 | WK2 | 12 |
| 2020Q4 | WK3 | 11 |
| 2020Q4 | WK4 | 15 |
Hi klehar your issue is that you are making the calculation only at quarter level so you are not abble to get the week level calculation correct.
Try to do the following steps:
- Create a new column:
Year + Q + W = 'Table'[Year Quarter]&'Table'[Weak]- Change your measure to:
Profit running total in Year Quarter = CALCULATE( SUM('Table'[Profit]), FILTER( ALLSELECTED('Table'[Year Quarter], 'Table'[Weak],'Table'[Year + Q + W]), ISONORAFTER('Table'[Year + Q + W], MAX('Table'[Year + Q + W]), DESC) ) )Result below and in attach PBIX file.
1 Reply
Hi klehar your issue is that you are making the calculation only at quarter level so you are not abble to get the week level calculation correct.
Try to do the following steps:
- Create a new column:
Year + Q + W = 'Table'[Year Quarter]&'Table'[Weak]- Change your measure to:
Profit running total in Year Quarter = CALCULATE( SUM('Table'[Profit]), FILTER( ALLSELECTED('Table'[Year Quarter], 'Table'[Weak],'Table'[Year + Q + W]), ISONORAFTER('Table'[Year + Q + W], MAX('Table'[Year + Q + W]), DESC) ) )Result below and in attach PBIX file.