Group Headers and Footers on a table

[PBIUser]

OK,

New BI user here, but familiar with other BI Reporting tools (Birt, SRSS, Crystal.) I have a table which looks like this:

I want to group the data by the Facility column (displaying the Value in the Facility column in the Group header)

I also want (in the group footer) to subtotal each of the Amt Submitted, Amt Paid and Balance columns - AND on the same row I want to display another column of data that I have - a summary note explaining the current status of the account.

I have figured out how to group (but cannot display the group header or footer.)

I can (using New Measure) create subtotals for Amt Submitted, Amt Paid and Balance columns (but grand totals - not subtotals and a grand total)

So I cannot see the group header (with the Group value), nor the group footer with the sub-totals and current status field)

This is a simple report in other products - simple, simple, simple! Why is it so hard (is it possible?) in Power BI?

My first experience with this product, and I know it is new, but it seems very non-intuititve and possibly - missing features. Please explain how I can accomplish what I need to do.

TIA,

[MarksmanWaugh]

@PBIUser did you ever solve this? 

[RobbieK]

Were you able to resolve this?  I understand COMPLETELY what you are trying to accomplish, and it appears (even in 2022) this is an impossible (or at least really poorly designed) to do in Power BI.

Greating a group, with a header, footer, and details in the middle is trivial in most reporting software.

[Phil_Seamark]

Hi @PBIUser

Are you using the Matrix visual to organise your data?  That should allow you to create subtotal lines. 

Do you have an image of what you would like your sample set of data to look like?  Perhaps mock up in Excel and post here.

[PBIUser]

No, I am using a table approach because I need to include the details of multiple columns - I'm not trying to summarize - at least I think that is why one would want to use the matrix approach.

Here is what I envisioned the report to look like - something I've accomplished easily in other products:

Should be very easy - the Current Status field is a field queried from the SQL table that would need to wrap in a field that would be about a third of the width of the Footer.

Thanks for your help.

[v-huizhn-msft]

Hi @PBIUser,

Based on the screenshot, you should use a matrix, and try to turn on the Subtotal button(highlighted in yellow) as follows.



Best Regards,
Angelia

[PBIUser]

Nevermind - I found it - However, please explain to me how I get the fields displayed in the order in my mock-up - I need the columsn for Patient Name, Staff, Open Date, DOD, DOS displayed, and then I need three columns with subtotals - Amt Submitted, Amt Paid, and Balance. 

What steps do I use to place those fields so that they appear in that order - I tried adding Patient Name, Staff and Open Date and only Patient Name appeared...

Help!

TIA,

[v-huizhn-msft]

Hi @PBIUser,

Please add Patient Name, Staff, Open Date, DOD, DOS  as row level fields. Then add Amt Submitted, Amt Paid, and Balance as value. Open the subtotal button, and check if it works fine.

Best Regards,
Angelia

[PBIUser]

Thanks for the help - but I can't see the Staff, Open Date, DOS, DOD and Insurance Co fields in the report.

Further, I need to group this report by Facility and I don't see how to use "groups" as it is grayed out. I want to include the name of the facility in the Group Header: something like this:

Does the above make what I am trying to do any clearer?

thanks,

[v-huizhn-msft]

Hi @PBIUser,

If your first problem has been resolved, please mark the right reply as answer. In addition, I personally suggest you create a new thread if you have new issue. One thread includes one problem. Thanks for understanding.

Thanks,
Angelia

[PBIUser]

Thanks, but I don't have that option - or I can't find it:

Why?