Forum Discussion

Smackcat's avatar
Smackcat
Icon for Helper I rankHelper I
1 year ago
Solved

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

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 measu...
Fabric
Measures
powerbi
reportbuilder
  • Anonymous's avatar
    Anonymous
    1 year ago

    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-aggregate-functions-reference?view=sql-server-ver16 

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

    Regards,
    Pallavi.



Smackcat's avatar
Smackcat
Icon for Helper I rankHelper I
1 year ago

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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-aggregate-functions-reference?view=sql-server-ver16 

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

Regards,
Pallavi.