Forum Discussion
Add individual analysis to each small multiple visual
Hi I have data from a single dataset that I visualised using the clustered column chart as you see above, I am using the year of each data point to split it up in the visuals by adding it to the year to the 'small multiples' field. I am tying to add min/max/avg to each year based on data only for that year, how can i do this? I presently seem to have these statistics in each visual but for the entire dataset.
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,
7 Replies
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 DataNinja,
Thanks for replying to my query, I think you have the right answer but the way I've choosen variables/how the database is set-up is probably stopping me from getting the visuals to come out right, the DAX works and I am able to get the right numbers to appear on a matrix, but I can't seem to get the appropriate lines that represent these numbers on the visual. My gut feeling is that this has to do with the DAX having Day of Year in it, this column is a duplicate of the original date column(from which i get year) which was transformed to day of year.Pic below shows settings for the column chart
Pic below shows settings for the Matrix - which has the righ values
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.
Hi atr1,
The reply shared by DataNinja777 is absolutely correct.
You need to create DAX measures that calculate Min, Max, and Average based on the Day of Year within the context of each year.
The provided AVERAGEX, MAXX, and MINX measures ensure that each small multiple visual will show statistics based only on the year it represents.
After creating these measures, you can add them as constant lines using the Analytics pane of your clustered column chart.
This will fix the issue you were facing where stats were showing for the whole dataset instead of year-specific.
Hope my suggestion gives you good idea, if you have any more questions, please feel free to ask we are here to help you.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you.
