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

[Smackcat]

Hi,

I've connected an RDL file in Report Builder to a semantic model developed in Microsoft Fabric. Basic measures like SUM work as expected. However, I'm encountering issues with more complex measures such as DIVIDE(Sum1, Sum2) and COUNTDISTINCT(ID), particularly in the total rows.

When I don't apply a specific formula in the report, the total row displays First(measure) instead of recalculating the total as needed. For the DIVIDE issue, I understand that applying a formula directly in Report Builder can resolve it, which is acceptable. However, I have several complex measures to work with, like COUNTDISTINCT(ID), where retrieving all ID values would exceed my Fabric capacity, making it impractical to perform a distinct count directly in the report.

Is there a feasible solution for this? Do you have any advice on handling such scenarios? Additionally, are there alternative tools for creating custom tables for export purposes, other than Power BI, which tends to slow down with complex measures

and large datasets?

[v-pagayam-msft]

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-aggr... 

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.

[Smackcat]

Hi!

Those both measures are already on the model, neverthless they don't make an accurate calculation on the total row on Report Builder, they do work well on the PBI report. 

Thanks

[v-pagayam-msft · Accepted solution]

Hi @Smackcat ,
Thank you for the follow-up.I would be happy to assist you!

• In Report Builder, totals often summarize already evaluated row values instead, leading to incorrect results for complex measures.
• For DIVIDE, try using this expression as a workaround in Report Builder:
    =IIF(Sum(Fields!Sum2.Value) = 0, 0, Sum(Fields!Sum1.Value) / Sum(Fields!Sum2.Value))
  
  
• For DISTINCTCOUNT and other complex logic, it is best to pre-aggregate the data using Dataflows Gen2, Fabric Notebooks, T-SQL queries
• Then, load the summary into a semantic model and use it in the report.This might avoid Report Builder having to recalculate heavy logic at the visual level.
  
  

Refer the document for more information:https://learn.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-aggr... 

Hope this resolve your query.If so,consider accepting it as solution.

Regards,
Pallavi.