Measure with Divide from a dataset

[Doby42]

I connected to a dataset with some basic measure.  Adding the measure in a table with report builder, by default, adds "sum".  This works fine when I have measure that are sum anyway.  But I have one measure that divides two other sums to figure out the % discount.  Report builder is still putting sum in front of that measure reference from the dataset and my summarized output is wrong. This measure works fine in a table in BI desktop.  I think its calculating the divide function on the individual rows then adding it up together in the table that I am showing.  Instead of what BI Desktop does which is take the sum of the values in the summarized table output and then divide.

Is there a way to get the same behavior from the report builder table as I get using that measure in the Desktop table for BI reports?

[FarhanAhmed · Accepted solution]

You need to regenerate the formula at the Reporting Builder Table/Matrix. Because it is not a good practrice to SUM or AVG or any other aggregation to the measures that are returning Percentages or ratios.

In power bi desktop the measure calculating on run time. But in report builder you have all your dataset comes first and then it render in the report. That means that if you have discount percentages at product Level. It will not return you discount percentages at Product Category level as your dataset contains further granular data in the dataset. So it is best to return Values in the dataset and recreate percentage or ratios formula at Report Builder cell level thats where you will get the correct results.

I hope I have clarifed to you.