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
JuSlaughter70
Frequent Visitor

SSRS Table Style Grouping

Hi

 

Total newbie to Power BI, been using SSRS for 13 years. We are currently in the process of doing an evaluation of Power BI and I can totally see how it can be of benefit when creating reports and dashboards. However, quite a few of our reports are just simple tables with groups and subtotals. Super easy to do in SSRS but for the life of me, I can't see a way of doing in the Power BI.

 

So for example, I have a Fund field, A Fund Name field and several text\numeric values. I want my report to look like this...

 

Fund       Fund Name           Year           GBP             Value           Perc                  Total

Fund A

               Fund Name A          2016          GBP          1000.00          0.4%           50000.00

               Fund Name B          2012          USD          2500.00          1.2%         127500.00

               Fund Name C          2018          EUR        10000.00          2.7%             9800.00

               Subtotal                                                 12500.00         1.4%        187300.00

Fund B

               Fund Name A          2017          GBP          2500.00          1.2%             5740.00

               Subtotal                                                   2500.00          1.2%           5740.00

 

Grand Total                                                           15000.00          1.3%         193040.00

 

As I say, doing this in SSRS is easy by first adding a Parent Group on Fund and then a Child Group on Fund Name and including footer rows for subtotal and grand total.

 

I have tried with a PBI table but can't seem to group. I have tried with a Matrix but only seem to get FIRST() when there could be many fund names within a fund, so am stuck. I did a search for Groups in PBI but looks like that it for creating custom groupings. It is probably really easy to do but just not that intuitive to an old dog like me ;o)

 

EDIT: Forgot to add, data comes from SQL Server views

 

Any help would be greatly appreciated.

 

Thanks

 

Julian

1 ACCEPTED SOLUTION
JuSlaughter70
Frequent Visitor

Morning

 

After a bit of playing around yesterday, I managed to produce something that looks almost like what I wanted.

 

I created a Matrix, used Fund and Fund Name in the Rows and then included all my other fields as Values. I think clicked on the "pitchfork" icon and set Stepped Layout to On.

 

I think I have just got to get used to things being different in Power BI, which is going to be difficult after 13 years of SSRS. I have to say, some of the processes for things like simple formatting aren't intuitive but I can see potential ;o)

 

Thanks

View solution in original post

3 REPLIES 3
JuSlaughter70
Frequent Visitor

Morning

 

After a bit of playing around yesterday, I managed to produce something that looks almost like what I wanted.

 

I created a Matrix, used Fund and Fund Name in the Rows and then included all my other fields as Values. I think clicked on the "pitchfork" icon and set Stepped Layout to On.

 

I think I have just got to get used to things being different in Power BI, which is going to be difficult after 13 years of SSRS. I have to say, some of the processes for things like simple formatting aren't intuitive but I can see potential ;o)

 

Thanks

v-lili6-msft
Community Support
Community Support

HI, @JuSlaughter70

You may try to use Drill mode in a visualization and drag nonaggregate field into ROW or Column instead of Value now.

Reference:https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.