Forum Discussion
Add individual analysis to each small multiple visual
Hi atr1 ,
To ensure that the Min, Max, and Average lines in your Power BI small multiples reflect values specific to each year, you need to create DAX measures that dynamically calculate within the filter context of each visual. By default, Power BI may calculate these statistics across the entire dataset unless you explicitly define the logic to respect the context introduced by small multiples.
Use the following DAX measures to calculate the average, maximum, and minimum counts of unique IDs per day within each year. These measures iterate over each day of the year within the current year context and compute the desired statistics accordingly.
AveragePerYear = AVERAGEX( VALUES('YourTable'[Day of Year]), CALCULATE(COUNTROWS(VALUES('YourTable'[Unique ID]))) )MaxPerYear = MAXX( VALUES('YourTable'[Day of Year]), CALCULATE(COUNTROWS(VALUES('YourTable'[Unique ID]))) )MinPerYear = MINX( VALUES('YourTable'[Day of Year]), CALCULATE(COUNTROWS(VALUES('YourTable'[Unique ID]))) )Replace 'YourTable', 'Day of Year', and 'Unique ID' with the actual names used in your model. Once these measures are created, go to the Analytics pane for your clustered column chart and add constant lines using these measures. Power BI will automatically respect the year context introduced by the small multiples, so each visual will show lines based only on the data for that specific year.
Best regards,
Hi atr1,
Since your matrix is showing correct values but the constant lines aren't appearing properly, it suggests the measures are still being calculated at a lower granularity (like 'Day of Year') rather than at the Year level.
To resolve this:
Make sure your measures calculate only at the Year level, without being impacted by 'Day of Year'.
You can use SELECTEDVALUE('Date'[Year]) inside your measure to ensure it responds to the Year context.
When adding constant lines, confirm that your Small Multiples field is set correctly to Year, and that the measures return a single value per Small Multiple.
Once the measure returns one value per Year, Power BI will allow you to add dynamic constant lines correctly inside the visual.
Please Accept as solution if this meets your needs and a Kudos would be appreciated.
Thank you.
Hi atr1,
As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.
If the issue has been resolved, please mark the helpful reply as a "solution" to indicate that the question has been answered and to assist others in the community.
Thank you for your cooperation. Have a great day.