Report Builder Matrix Totals Incorrect

[Anonymous]

Hi,

I am trying to create a matrix in Report Builder that I have in Desktop. I copied the DAX query for the table and added it as a dataset in Report Builder.

Some of the columns rely on measures to calculate averages and these work fine however I noticed for row totals it does not use the measure to get a correct total, I can only use Report Builters aggregate functions (like Sum, AVG, etc) the problem is this gives the incorrect total. For example, it will average the results for the sub-rows which gives an incorrect average, i need the measure calculation to run on the total data for the period like it does in PBI Desktop. This does not occur.

Any idea on how to get this working correctly?

[Anonymous · Accepted solution]

To anyone else new to this all, i fixed it but it meant re-thinking my DB. You have to do these calcs IN report builder instead of relying on the measures to do your totals correctly (they won't).

Instead i added new measures that gave SUM totals in dataset that were needed to perform the calculations in powerBI Report Builder. I then just applied formulas on those columns. Problem solved.

So, just rethink your data when moving reports to Report Builder