Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
98 | |
97 | |
38 | |
38 |
User | Count |
---|---|
153 | |
122 | |
76 | |
73 | |
66 |