Paginated Reports/SSRS not reporting calculated percentages or per unit correctly

[PeteSmith6730]

Hi, not sure whether this is the board for this, if you need me to post elsewhere then please let me know.

I have been developing some reports with the power bi report builder for some time. Fantastic tool and overcomes some of the formatting issues lacking in power bi (although I know they're coming)

I am connecting to an SSAS tabular model (little bit faster for dev than connecting to PBI service).

I have a report with Column Groups which report detail on 5 sites. The totals column is working fine for summing of data, but is not working averages through. It is just summing the 5 averages rather than recomputing the true average. Measure in PBI destop works correctly.

So how do I get the report builder to compute anything that is not a sum (averages, per unit calcs, percentages, margin percentages etc.) I have managed all this is in power bi with measure with a switch function to call other measures so it is rather complex.

Any help appreciated

Pete

[PeteSmith6730 · Accepted solution]

Managed to fix this one!!! Yeah!!

by bringing in a separate dataset from ssas at the summary level. Then with ssrs lookup function the totals on right columns can referenced from the second dataset.

this is probably not an optimised solution but the reps I am writin will be snapshot generated once a month so can handle slow speed.

[v-xuding-msft]

Hi @PeteSmith6730 ,

We can also create complex expressions with the function of switch in Report Builder. You can reference the documents below to check and modified your formula.

Expression examples in Power BI Report Builder

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.