The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I can usually find a similar post that is close enough to what I am looking to do and tweak it to get where I need, but I'm coming up short this time.
I have this visual.
The darker line is [Cumulative Actual], and the ligther dotted line is [Cumulative Forecast] (which is actually actuals + forecast, lying underneath the darker line so as to hav e continuous line).
Cumulative Actual =
CALCULATE(
[Cumulative Forecast],
'Consolidated_TDP_Data'[Act/Fcst] IN { "ACTUAL" }
)
Cumulative Forecast =
CALCULATE(
SUM('Consolidated_TDP_Data'[Value]),
FILTER(
ALLSELECTED('Consolidated_TDP_Data'[Month/Year]),
ISONORAFTER('Consolidated_TDP_Data'[Month/Year], MAX('Consolidated_TDP_Data'[Month/Year]), DESC)
)
)
The reference line is the Goal (Funded).
Current_Funding = Sum('OY2 MIPR Breakout thru Mod 22'[Current Funding])
There are a few filters that can be applied to the visual to get to just the Actuals/Forecast/Goal for one project or one type of spending, and that all works fine.
What I'd like is to add an X-Axis Reference line at the first point where the Forecast is > 50% of the Goal/Funded line. So, in the photo above, 50% of the Goal would be $23.8M, so the refence line should shoot up from where i have my cursor, which is the first month where 50% of the goal was exceeded.
Thanks in advance.
Solved! Go to Solution.
You could define a measure like
Passes halfway =
VAR Goal = [Current_funding]
RETURN
FIRSTNONBLANK (
'Date'[Date],
IF ( [Cumulative forecast] > DIVIDE ( Goal, 2 ), 1 )
)
and then use that as the value for an x-axis reference line
You could define a measure like
Passes halfway =
VAR Goal = [Current_funding]
RETURN
FIRSTNONBLANK (
'Date'[Date],
IF ( [Cumulative forecast] > DIVIDE ( Goal, 2 ), 1 )
)
and then use that as the value for an x-axis reference line
This did the trick. Oddly, when I named the variable Goal like you had it, I got a, error stating "The syntax for Goal is incorrect". I changed the variable name to Funding and that solved it. I also added a 75% line. Thanks for the help!
Hi @Anonymous ,
To create a vertical reference line, you need to create a "line and stacked column chart".
Create a measure.
Measure =
IF (
'Consolidated_TDP_Data'[Cumulative Forecast]
> RELATED ( 'OY2 MIPR Breakout thru Mod 22'[Current Funding] ) * 0.5,
'Consolidated_TDP_Data'[Month/Year]
)
Then Put column [Month/Year] into shared axis and put [Cumulative Actual] and [Cumulative Forecast] into line values.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Community Support Team _ xiaosun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |