Average of percents calculation incorrect in bar graph

[claireberry]

Thanks for your attention. The average of a column with percentages in the y-axis field for a clustered column chart shows incorrect value. The below image shows the average calculation. But when I filtered the column with percentage values, I get a number slightly larger than what is shown in the graph. The actual average of these values is 42.4%, but the column chart shows 36%

 

[Ritaf1983 · Accepted solution]

Hi @claireberry 

It’s difficult to fully understand the issue from the textual description, and the screenshot does not clearly demonstrate the data structure or how the average is being calculated. To provide a precise solution, I recommend sharing the PBIX file via a public cloud (e.g., OneDrive or Google Drive) and including a link.

As a general rule, when working with percentages, dragging a percentage column directly into the Y-axis is not advisable. Instead, use a DAX measure that dynamically adjusts calculations based on the hierarchy level using functions like:
ISINSCOPE(), HASONEVALUE(), or HASONEFILTER().

Example :
Avg_Percent :=
IF (
ISINSCOPE('Table'[State]),
AVERAGE('Table'[Percent]),
DIVIDE(SUM('Table'[Numerator]), SUM('Table'[Denominator]))
)

You can also take a look at the linked videos :
https://www.youtube.com/watch?v=So6vr3mTHsA

https://www.youtube.com/watch?v=ek13VBQQWks&t=109s

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[v-veshwara-msft]

Hi @claireberry ,
Just wanted to check if the responses provided were helpful.  If further assistance is needed, please reach out and share sample data or .pbix file to assist better.
Thank you.

[v-veshwara-msft]

Hi @claireberry ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out. Please share small sample dataset or mock PBIX file to assist better.
Thank you.

[v-veshwara-msft]

Hi @claireberry ,

Just checking in to see if you query is resolved. If further assistance is needed, please reach out. Please share small sample dataset or mock PBIX file to assist better.

Thank you.

[v-veshwara-msft · Accepted solution]

Hi @claireberry ,

Thanks for reaching out to Microsoft Fabric Community.

Thanks to @Ritaf1983 and @MFelix  for the response and explanation.

In addition to what's already shared, this kind of mismatch is often due to how visuals aggregate values at different levels.

For example, visuals like clustered column charts may compute the average based on the grouped data points shown (like per category), whereas a table or card might calculate the average across all rows, giving equal weight to each.

To correct this behavior and ensure consistency across visuals, you can use functions like AVERAGEX to align the results as needed.

Similar discussion: Solved: Average is incorrect.... between the bar graph ave... - Microsoft Fabric Community

To reproduce this and assist further, could you share a sample PBIX (without any sensitive data).

Hope this helps. Please reach out for further assistance.

If any response helped, please consider marking it as Accepted Solution to assist others with similar queries.

Thank you.

[Ritaf1983 · Accepted solution]

Hi @claireberry 

It’s difficult to fully understand the issue from the textual description, and the screenshot does not clearly demonstrate the data structure or how the average is being calculated. To provide a precise solution, I recommend sharing the PBIX file via a public cloud (e.g., OneDrive or Google Drive) and including a link.

As a general rule, when working with percentages, dragging a percentage column directly into the Y-axis is not advisable. Instead, use a DAX measure that dynamically adjusts calculations based on the hierarchy level using functions like:
ISINSCOPE(), HASONEVALUE(), or HASONEFILTER().

Example :
Avg_Percent :=
IF (
ISINSCOPE('Table'[State]),
AVERAGE('Table'[Percent]),
DIVIDE(SUM('Table'[Numerator]), SUM('Table'[Denominator]))
)

You can also take a look at the linked videos :
https://www.youtube.com/watch?v=So6vr3mTHsA

https://www.youtube.com/watch?v=ek13VBQQWks&t=109s

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[MFelix]

Hi @claireberry ,

This can occur due to different things, depending on the way you have your model built and the visualization.

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.