I'm trying to recreate a power bi report (Power BI Desktop) in Power Bi Report Builder where I'm using a non-additive measure. I have read that I can use Report Builder's Aggregate function to move the aggregation to the server side and this way use my DAX measure from the model but I fail to get the same result as I get in Power BI.
My test data table in Power BI (named 'Table'):
My measure:
Measure = SUMX(SUMMARIZE('Table', 'Table'[Step], "X", AVERAGE('Table'[sec])), [X])
Matrix report create in Power BI:
After I publish the report to MyWorkspace in Power BI online I can add a connection to it in Report Builder and create a dataset with a DAX query (using Query Designer):
The resulting DAX query is this:
EVALUATE SUMMARIZECOLUMNS('Table'[Date], 'Table'[Step], 'Table'[sec], "Measure", [Measure])
The by using the New Matrix wizard I can crate a Tablix like this:
I'm expecting to get the same result as I got in Power BI Desktop, but I get the following:
Can Aggregate be used here or is there another way to calculate a Power BI models measures value for each cell of a Report Builder Tablix?
