Average for each small multiple

[MuthalibAbdul]

I am trying to have individual monthly average for each location.

x axis is Calender[Month]

Y axis is FinalTable[Patient Name]

Small multiples is FinalTable[Location]

I cannot use sum since the patient name column is text. I tried count rows function but not able to figure out the code along with Month and Location

[danextian · Accepted solution]

Hi @MuthalibAbdul 

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

[MuthalibAbdul]

This solutions worked like a magic. THANK YOU!!! However, I am not able to position the average to the right of the bar. I labels at every change of month as shown below. I have tried going through your visual option by option and yet failed to contain the labels on the average bar. Can you help with this too?

[MuthalibAbdul]

The output you showed is exactly how I want my report to be. However I am not getting the same result. in the code above, what is [row count]?

[danextian]

Have you checked the pbix? The details are there. It's just the count of rows in a table. 

[MuthalibAbdul]

I am using this Version: 2.139.1678.0 64-bit (January 2025). The file you attached is not supported. 

[lbendlin]

This may be possible with a Deneb visual.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[burakkaragoz]

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.

[MuthalibAbdul]

Thanks, I think we are still missing the monthly averages for each facility

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.