Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Estrobelai
New Member

Full Year Forecast stacked bar graph in Power BI

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:

 

  • Booked work
  • Run Rate Revenue
  • Likely Potential work (70%)
  • Other identified (30%-70%)
  • Other Forecast Revenue

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

 

test.png

 

 

6 REPLIES 6
Greg_Deckler
Super User
Super User

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 (>).


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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:

 

http://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/...

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.