Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
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])
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?
RunningTotal =
CALCULATE(
[MonthTarget];
FILTER(
ALLSELECTED('Calendar'[Month]);
ISONORAFTER('Calendar'[Month]; MAX('Calendar'[Month]); DESC)
)
)
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])
Happy to help! 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
108 | |
92 | |
67 |
User | Count |
---|---|
167 | |
130 | |
129 | |
94 | |
91 |