How to sum amounts in PBI Report Builder when source is PBI dataset with pivoted columns?

[Trudgeon]

I'm trying to re-build a PBI Desktop report in PBI Report Builder to make it a paginated report for use in Power Automate.  The Desktop report includes a pivoted column which turns into six individual columns post-pivot.  In order to display things properly in Desktop, I selected the "Sum" setting for the six columns...

For PBI Report Builder data source, I'm using the PBI dataset from the Desktop report.  What results is a hideous display of duplicate lines for the six post-pivot columns (see below).  I need to be able to roll them up them similar to the Desktop report.  How would I do so?

[Trudgeon]

Thank you, @OwenAuger 

It worked perfectly!

Best regards,

Chad

[OwenAuger · Accepted solution]

Hi @Trudgeon 

For Power BI Report Builder, you will need to create a query that groups by the first column in your screenshot, and creates six aggregated columns corresponding to the other columns.

It sounds like you used implicit measures in Power BI Desktop (by ticking Sum) so, without creating any further measures, you could modify your existing DAX query in Power BI Report Builder to look something like this:

EVALUATE
SUMMARIZECOLUMNS (
    Table[Grouping Column],
    "Col 1", SUM ( Table[Col 1] ),
    "Col 2", SUM ( Table[Col 2] ),
    "Col 3", SUM ( Table[Col 3] ),
    "Col 4", SUM ( Table[Col 4] ),
    "Col 5", SUM ( Table[Col 5] ),
    "Col 6", SUM ( Table[Col 6] )
)

where Col 1 to Col 6 are the columns to be summed.

Regards,

Owen