Issues with Totals in Report Builder When Using DIVIDE and COUNTDISTINCT Measures

Hi Smackcat ,
Thank you for reaching out to us on Microsoft Fabric Community Forum!

• A reliable way to fix this is by defining your complex measures directly in the semantic model. That way, the logic is handled by the engine itself, and totals are calculated correctly.
• For example, a DIVIDE measure can be written like this in the model:     DivideMeasure := DIVIDE(SUM('Table'[Sum1]), SUM('Table'[Sum2]), 0)
  
  
• This ensures both row-level and total-level calculations work the same way.
• For distinct counts, define a measure like this:CountDistinctID := DISTINCTCOUNT('Table'[ID])
• Trying to calculate that in Report Builder, especially on large datasets, can overwhelm your Fabric capacity, so handling it in the model is much more efficient.
• If you're not able to change the model and need a quick workaround in Report Builder, you can try a basic expression like:

               =IIF(Sum(Fields!Sum2.Value) = 0, 0, Sum(Fields!Sum1.Value) / Sum(Fields!Sum2.Value))

• Just note that this works only for simple aggregations and can become unreliable with more complex or large-scale calculations.
• Since distinct count calculations can be heavy, it’s a good idea to pre-aggregate them before loading into the model. You can use tools like Dataflows Gen2 or Fabric Notebooks to calculate these upstream and keep the reporting layer light.
• If performance or capacity is a concern, you could also explore partitioning large lakehouse tables or using Direct Lake mode (if applicable) to reduce the strain during queries.
• For exporting large or complex tables, Power BI can struggle a bit, so here are a few alternative tools that might work better depending on your needs:
• Notebooks in Fabric: Great for using Python or Spark to calculate, transform, and export data (e.g., to CSV or Parquet). Very helpful for large datasets or programmatic workflows.
• Dataflows Gen2: Ideal if you prefer a low-code, UI-based way to transform and pre-aggregate your data before reporting.
• T-SQL endpoints: If you're using a lakehouse or warehouse, you can run SQL queries for aggregations and export results directly.
• Paginated Reports with pre-aggregated data: Still a solid option if you keep the dataset lean and do heavy lifting upstream.
  

Refer the link below:
https://learn.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-aggregate-functions-reference?view=sql-server-ver16 

If you continue using Power BI for exports, consider optimizing the model by reducing unused columns, minimizing high-cardinality fields, and using aggregations.

If the answer meets your requirement,consider accepting it as solution.

Regards,
Pallavi.