Add individual analysis to each small multiple visual

[atr1]

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.

[v-sgandrathi]

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.

[DataNinja777 · Accepted solution]

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,

[atr1]

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

[v-sgandrathi]

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.

[atr1]

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

[v-sgandrathi]

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.

[v-sgandrathi]

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.