Produce Average Line in Small Multiples Visual

TomTomTom · ‎10-03-2022

Hello,

I have a Small Multiples visuals that shows three measures. Two are y-axis columns, one is a y-axis line.

I also want to add in a flat line that is the average of one measure across the whole time period.

I cannot seem to figure it out ... any tips?

(I figure I need to remove the filter on the date, but I don't seem to be able to get it working...)

parry2k · ‎10-03-2022

@TomTomTom you need to add it as a new measure and use it in the visual instead of updating existing measures.

✨ Follow us on LinkedIn and to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

TomTomTom · ‎10-03-2022

Yes, that is what I did ...

parry2k · ‎10-03-2022

@TomTomTom ok then add this measure for flat line

Measure = 
CALCULATE ( [Your Measure], ALLSELECTED ( DateDimensionTable ) )

✨ Follow us on LinkedIn and to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

TomTomTom · ‎10-10-2022

Just to say this problem remains unresolved.

If anyone ( @parry2k - ?) has some fresh ideas that'd be great.

My thought is that when using small multiples, somehow one measure can change the filter context for other measures. Maybe the solution is to roll-up/group-by the data set in some way with calculated columns before bringing it into the visiaul. Although I think this would then break the connection with the data model, so it'd be quite an ugly fix.

I have a while before I need to implement the constant lines... I'll keep trying ideas for a 'good' solution.

TomTomTom · ‎10-03-2022

Mm, that just seems to completely scramble the visual ... all the other measures suddenly change to the same value ...

parry2k · ‎10-03-2022

@TomTomTom do you have a seperate dimension table for dates that you are on using on the x-axis? It all depends on how your model looks like. Please provide the details on it for the solution.

✨ Follow us on LinkedIn and to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

TomTomTom · ‎10-03-2022

Hi, I do have a separate date table. This produces the x-axis.