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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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