Forum Discussion
Running total
Hello everybody,
I have a CALENDAR table in my data model. I have too two more tables: WAREHOUSES and TARGETS:
| ID | WAREHOUSE |
| A | Warehouse1 |
| B | Warehouse2 |
| C | Warehouse3 |
| YEAR | WAREHOUSE | TARGET |
| 2017 | A | $ 5.000,00 |
| 2017 | B | $ 10.000,00 |
| 2017 | C | $ 20.000,00 |
| 2016 | A | $ 6.000,00 |
| 2016 | B | $ 25.000,00 |
| 2016 | C | $ 18.000,00 |
The TARGETS table is not related with de CALENDAR table but is related with the WAREHOUSES table. I have too a measure MonthTarget = SUM(Target)/12
I need the running total for Target field in this matrix visualization:
| YEAR | 2017 |
| WAREHOUSE | A |
| Month | Target |
| January | $ 416,67 |
| February | $ 833,34 |
| March | $ 1.250,00 |
The YEAR and WAREHOUSE fields are filters and the value of 416,67 is the MonthTarget value of the A warehouse for the year 2017. For a whole year it would be:
| YEAR | 2017 |
| WAREHOUSE | A |
| Month | Target |
| January | $ 416,67 |
| February | $ 833,34 |
| March | $ 1.250,00 |
| --- | $ 1.666,67 |
| --- | $ 2.083,34 |
| --- | $ 2.500,00 |
| --- | $ 2.916,67 |
| --- | $ 3.333,34 |
| --- | $ 3.750,00 |
| --- | $ 4.166,67 |
| --- | $ 4.583,34 |
| December | $ 5.000,00 |
How can I calculate this measure?
Thank you.
In looking at your data layout and calculation, I would create a MONTH column in your Calendar table and do something like:
Measure = MonthTarget * AVERAGE(Calendar[Month])
7 Replies
Have you tried the Running Total quick measure?
Yes, but the result is the same value of the month target for every mont, like this:
Month Target RunningTotal January $ 416,67 $ 416,67 February $ 833,34 $ 416,67 March $ 1.250,00 $ 416,67 Can you post that formula?
