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,
The DAX formulas are correct and returning the right statistics (Average, Min, Max) for each year as we can see from your matrix visual. The challenge is in adding them as constant lines on the column chart because the measures are based on 'Day of Year', making them behave per data point instead of a single value for the visual.
You will need to create new DAX measures that calculate the Min, Max, and Average at the Year level only without referencing 'Day of Year'.
Try this Measure:
Average_Count_Per_Year =
AVERAGEX(
VALUES('YourTable'[Unique ID]),
1
)
Min_Count_Per_Year =
MINX(
VALUES('YourTable'[Unique ID]),
1
)
Max_Count_Per_Year =
MAXX(
VALUES('YourTable'[Unique ID]),
1
)
Then, in the Analytics pane, you can add dynamic constant lines by selecting these measures.
If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!
Thank you.
Unfortunatley this does not work and returns 1 for every year in the matrix. I realised that using the origindal date column(same column used in the multiples field) in the DAX data ninja quoted works to give the same results on the matrix as per my latest screenshots, but I still can't get the chart to show lines based on a specific year
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.