cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amorrow98
Helper I
Helper I

Line Charts and SWITCH Function

Hi All, 

 

I am creating a line chart to show a trend line for company specific metrics. I have used the SWITCH function to construct the formula seem below: 

Formula =
VAR Actual_Metric = DIVIDE(SUM(Budget[Budget]),SUM(Actual1[Actual])
VAR FCST_Metric = DIVIDE(SUM(Budget[Budget]),SUM(Forecasted_Actual1[Actual])
RETURN
SWITCH(
TRUE(),
[Max_Actual1_Date] >= SELECTEDVALUE(s_Date[Date]),        Actual_Metric,
[Max_Actual1_Date] < SELECTEDVALUE(s_Date[Date]),          FCST_Metric,
                                                                                                FCST_Metric
)

The goal here is to use 'SUM(Actual1[Actual])' for the dates that are in our table and for anything outside of that range, use the 'SUM(Forecasted_Actual1[Actual])' data. This formula works on a table at a row level but when using on a clustered line/column chart it does not extend the line at a monthly level to the future dates where the FCST_Metric func. would be evaluated.

For example. the months of November and December do not display the line at a monthly level but if filtered down to a daily level the line magically shows up. I am trying to understand/fix this issue so that this line is shown at a monthly level and continuous, currently it appears disjoint despite the data itself being continuous. Additionally, the measure is summing the total daily values on my CARD visual whereas it should be the aggregated average. Any fixes on this for my formula? 
 
Additional Context:
s_Date is a M-language created date table that feeds all tables in my schema. The Budget, Actual1 and Forecasted_Actual1 tables are all connected to the s_Date table. The measure [Max_Actual1_Date] returns the max date from the Actual1 table. 

Thank you for your time and patience! 
 
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User


@amorrow98 wrote:
For example. the months of November and December do not display the line at a monthly level but if filtered down to a daily level the line magically shows up.

The SELECTEDVALUE function will return a blank if there are multiple values within the current filter context. If you're grouping at the month level, then you'll get a blank since there isn't a single value to return. You need to tell it what date you want to compare to when multiple are available. One way to do this is to use MAX instead of SELECTEDVALUE.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User


@amorrow98 wrote:
For example. the months of November and December do not display the line at a monthly level but if filtered down to a daily level the line magically shows up.

The SELECTEDVALUE function will return a blank if there are multiple values within the current filter context. If you're grouping at the month level, then you'll get a blank since there isn't a single value to return. You need to tell it what date you want to compare to when multiple are available. One way to do this is to use MAX instead of SELECTEDVALUE.

View solution in original post

Hi Alexis, this fixed both of my problems, thank you for the tip and insight. I will remember this for the future! 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.