Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I've been stuck on something and would appreciate if someone could have a look and help.
I want to create a clustered column chart that shows Actual vs Forecast amounts. However, I want it to ONLY show Actual OR Forecast with the following condition:
If actual amount exists for a given month, show me a bar that shows the actual amount
If actual does NOT exist (no data), then show me a bar that shows the forecast for that given month.
Let's assume the fields are: Table1[Actual Amount] and Table1[Forecast Amount], and the month field is Table1[Month]
Thanks!
Solved! Go to Solution.
If you want a single measure, I would do something like:
Actual or Forecast :=
VAR Act = SUM(Table1[Actual Amount])
RETURN
IF (Act, Act, SUM(Table1[Forecast Amount]))
If you want 2 measures... with forecast returning blank if there is an actual...
Actual := SUM(Table1[Actual Amount])
Forecast := IF ([Actual], BLANK(), SUM(Table1[Forecast Amount]))
If you want a single measure, I would do something like:
Actual or Forecast :=
VAR Act = SUM(Table1[Actual Amount])
RETURN
IF (Act, Act, SUM(Table1[Forecast Amount]))
If you want 2 measures... with forecast returning blank if there is an actual...
Actual := SUM(Table1[Actual Amount])
Forecast := IF ([Actual], BLANK(), SUM(Table1[Forecast Amount]))
Wow your solution totally works. One more question now (I'm getting greedy haha!):
I have plotted actuals and forecasts for 12 months in the chart...is there a way to automatically draw some sort of a line in between where actuals end and forecasts begin?
Now you have gone beyond my skillz. I just write fancy DAX formulas 🙂
LOL, what you have provided me has helped a whole bunch. Much appreciated! Thank you
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |