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 ,
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.
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.
