Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have this Full Year Forecast stacked bar graph on my dashboard that you can see below. This has been stacked because the company I work for splits revenue forecast into different probabiltiies:
The actual revenues here are known as Booked Actuals.
It is also improtant to mention that there is a column on the database called "Actuality" which defines what type of account we are looking at - whether it's a forecast account (F1 or F2 or F3, etc) or actual (AC).
The Dashboard I am creating has 2 main slicers as you can see on the picture below: One for the reporting month (usually the month you are in) and another one for the forecast comparison.
Example: Say we are looking at February reporting and we want to compare this to the forecast previosuly made in January. In this scenario the user would select reporting month as "February" and "F1" for the Comparison Slicer (the comparison slicer will need to have available for selection F1,F2,F3,F4 and so on - until the month right before the one you are in - in this example we are reporting February - hence only F1 should be available.)
I would like to know if anyone could help me please with the creation of this stacked bar chart. When the user selects - say February - we should only see the actuals going up to February - and from March onwards only the forecast should be displayed. In this example only F1 would be available for selection. However, if March was selected, F1 and F2 should be both available. This logic is then repated throughout the year.
Any suggestions / help how to get this working would be much appreciated!
Thanks very much
Andre
It sounds like you need two measures that use a CALCULATE. The FILTER clause for the first would be all dates prior (<) to the SELECTEDVALUE of the slicer and the other would be those after (>).
Hello @Greg_Deckler,
Thanks very much for your prompt response! When you say 2 measures do you mean each one of these measures will drive a different slicer?
How would the DAX structure for both roughly look like in your perspective?
Thanks very much
Andre
No, I was thinking that the measures would be the Value that you would display in your visualization(s). I was thinking something along the lines of:
m_Measure1 = CALCULATE(SUM(Table[Column]),FILTER(ALL(Table),Table[Date]<SELECTEDVALUE(Table[SlicerColumn]))) m_Measure2 = CALCULATE(SUM(Table[Column]),FILTER(ALL(Table),Table[Date]>SELECTEDVALUE(Table[SlicerColumn])))
To account for a second slicer, you may have to use ALLEXCEPT instead of ALL.
Hello @Greg_Deckler,
Thank you for this! However the stacked chart allows for only one field as "Value". How to get around this if we want to have both measures in the visualisation?
Thanks
Eric
Hi @Estrobelai,
We can not add two measures in the "Value" level. Have you resolved your issue? If you have, please mark the right/helpful reply as answer. More people will benefit from here.
Best Regards,
Angelia
Ah, missed that. For that, you would probably need to use the Disconnected Table Trick. See my article here that demonstrates this technique:
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |