Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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:
Year + Q + W = 'Table'[Year Quarter]&'Table'[Weak]
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Year + Q + W = 'Table'[Year Quarter]&'Table'[Weak]
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |