Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Trudgeon
Helper III
Helper III

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

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...

 

image.png

 

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?

 

image.png

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
Trudgeon
Helper III
Helper III

Thank you, @OwenAuger 

 

It worked perfectly!

 

Best regards,

Chad

OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.