Forum Discussion
Average for each small multiple
It seems that you're using the average from "Add further analyses to your visual" which unforunately returns the average for all. If you want the average for each small multiple by month, create a measure
Monthly Average = AVERAGEX ( ALLSELECTED ( 'Date'[Month] ), [Row Count] ) Monthly Average Label = VAR _maxMonth = MAXX ( ALLSELECTED ( 'Date'[Month] ), [Month] ) RETURN IF ( SELECTEDVALUE ( 'Date'[Month] ) = _maxMonth, [Monthly Average] )You'll need to use a custom data label to be able to show the label only for a specific data point.
Please see the attached pbix
Hi MuthalibAbdul ,
Got it — since you're using Patient Name (a text column) on the Y-axis, you're likely trying to count the number of patients per month per location, right?
In that case, you can’t use SUM, but you can definitely use COUNTROWS or DISTINCTCOUNT depending on what you want to measure.
Here’s a DAX measure you can try:
Monthly Patient Count =
CALCULATE(
COUNTROWS(FinalTable),
ALLEXCEPT(FinalTable, FinalTable[Month], FinalTable[Location])
)Or, if you want to count unique patients:
Monthly Unique Patients =
CALCULATE(
DISTINCTCOUNT(FinalTable[Patient Name]),
ALLEXCEPT(FinalTable, FinalTable[Month], FinalTable[Location])
)Then use this measure on the Y-axis, with:
- X-axis: Calendar[Month]
- Small multiples: FinalTable[Location]
This will give you the monthly patient count per location, which is what I think you're aiming for.
Also, here’s a helpful doc on small multiples in Power BI:
🔗 https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-small-multiples
Let me know if you want to calculate averages instead of counts — happy to help with that too.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.
Thanks, I think we are still missing the monthly averages for each facility
Hi,
Share the download link of the PBI file.
