COLLAPSEALL not working for smaller numbers in a Visual Calculation

[plew]

I've been trying to write a visual calculation formula, but I've noticed that for smaller numbers it doesn't work as expected. 
I've been able to pin the problem to COLLAPSEALL behavior. It basically stops working for smaller numbers in my table. It works well for many rows, but when it reaches values smaller than 0.01% of the column total, the Total column shows the row value, instead of the expected Total value (collapsed column total).
The test formula is as simple as this:  Total = COLLAPSEALL([Value],ROWS)

The screenshot shows a data sample, where it breaks (rows sorted by value, descending). 

[v-sgandrathi]

Hi @plew,
Thankyou @burakkaragoz for the detailed explanation, you're absolutely right.

This issue with COLLAPSEALL occurs due to internal optimizations in Power BI, where very small values (less than ~0.01% of the total) may be excluded from the collapse logic, causing the total column to display the row value instead. Using regular DAX measures with REMOVEFILTERS, or forcing evaluation with SUMX(ALLSELECTED(...)), can help ensure consistent totals. It's also worth checking data types and precision to avoid rounding issues. Appreciate you highlighting this limitation and offering clear workarounds for the community.

Glad I could assist! If this answer helped resolve your issue, please mark it as Accept as Solution and give us Kudos to guide others facing the same concern.

Regards,

Sahasra

Community Support Team.

[v-sgandrathi]

HI @plew,

We wanted to follow up to see if our suggestion was helpful. Please let us know how things are progressing and if you are still encountering any issues.

If the response resolved your problem, you may mark it as the solution and give it a thumbs up to assist others in the community as well.

Thank you and continue using Microsoft Fabric Community Forum.

[burakkaragoz]

Hi @plew ,

This is an interesting edge case with the COLLAPSEALL function in visual calculations. What you’re describing is likely related to internal optimization and how Power BI handles very small numbers and totals when using COLLAPSEALL in visuals.

A few points to consider and possible workarounds:

---

1. Why does this happen?

• Power BI visuals, especially with visual calculations, may optimize or truncate values that are extremely small (e.g., less than 0.01% of the column total).
• When the value is very small, the engine sometimes skips or collapses these rows during the total calculation, resulting in the total column showing the row value itself instead of the grand total.

---

2. What can you try?

• Check data type and precision: Make sure your column’s data type is set to decimal and that sufficient precision is preserved in the source and in Power BI.
• Adjust the visual calculation: If possible, try wrapping your calculation in a SUMX to force row context, for example:
  dax

  Total = SUMX(ALLSELECTED('Table'), [Value])

• Test removing filters: Try using ALL instead of COLLAPSEALL to see if the behavior changes:
  dax

  Total = CALCULATE(SUM([Value]), ALL('Table'))

• Visual calculation alternative: Sometimes, moving the calculation to a regular measure (outside of a visual calculation) and then referencing that measure in your visual can resolve the issue.

---

3. Known limitations:

• There are a few known limitations and bugs with COLLAPSEALL and extremely granular values in large visuals. The behavior can also depend on how the visual is sorted and the overall dataset size.
• If you continue to see inconsistent totals, you might want to consider reporting this as feedback to Microsoft, especially if you can reproduce the bug in a small sample file.

---

Summary:

• This issue seems to be triggered by very small numbers and how visual calculations aggregate data.
• Try the workarounds above to see if you get more consistent results.
• If possible, please share a minimal PBIX file or more details, so the community can help further.

Hope this helps! Let us know if you find a workaround or need more detailed steps.

[DBPowerBI]

This may not solve your issue but it's how it is. 

When you use COLLAPSEALL in Power BI, it usually shows each row’s real value.
But if a row has a really tiny value, like less than 0.01% of the total, Power BI sometimes skips the real calculation to work faster.
Instead of the small number, it shows the total for that column.
This is just how Power BI handles very small numbers, to save time.
You’ll see this happen most often when you sort by value and look at the smallest rows.
It isn’t something you can fully fix, but you can try filtering out very small values if you don’t want this to show up.
It’s a known behavior and not a mistake in your formula.