Help with Subtotal in the standard table (cannot use Matrix)

[J_L]

Hi team,

Hope you're all doing well all things considered. 

I need help with creating (adding) a dynamic subtotal row in the standard table. I cannot use a Matrix because it will aggregate the rows and I require visibility of all associated line items that make up that subtotal.

I've tried this solution provided by @BILASolution but it didn't solve my problem (or I may not have understood the measure/DAX completely).

https://community.powerbi.com/t5/Desktop/Subtotals-in-a-table-that-has-multiple-attributes-in-each-d...

In my sample data below, I've provided a current state and a desired state. The Subtotal will need to be dynamic as there will be slicers (such as Date and Item, etc..)

Current State

Date	Day	Item	Sold	Unsold
1st jan	Fri	Shirt	23	12
1st jan	Fri	Pants	56	2
1st jan	Fri	Shorts	21	1
2nd jan	Sat	Shirt	12	2
2nd jan	Sat	Shorts	21	12
3rd jan	Sun	Pants	12	4
4th jan	Mon	Shirt	11	13
4th jan	Mon	Pants	32	54
4th jan	Mon	Shorts	54	32
Total			242	132

Desired state

Date	Day	Item	Sold	Unsold
1st jan	Fri	Shirt	23	12
1st jan	Fri	Pants	56	2
1st jan	Fri	Shorts	21	1
			100	15
2nd jan	Sat	Shirt	12	2
2nd jan	Sat	Shorts	21	12
			33	14
3rd jan	Sun	Pants	12	4
			12	4
4th jan	Mon	Shirt	11	13
4th jan	Mon	Pants	32	54
4th jan	Mon	Shorts	54	32
			97	99
Total			242	132

Any help will be greatly appreciated!!!

[Anonymous]

 The fact that in order to do the simplest thing possible - make a table with subtotals - without having to either use a control you don't want, or a hack, just reinforces that PowerBI is the sh*ttiest tool imaginable.

[parry2k]

@J_L glad it worked out, indeed, never hesitate to reach out.

Good luck!

[parry2k]

@J_L you should add a measure something like this for these text columns:

Text Measure = 
IF ( HASONEVALUE ( Table[ColumnOnTheRow] ), MAX ( Table[YourTextColumn] ) )

and use this measure in the visual, so basically on the total rows, it will show as blank(), or other trick is, in format pane, under filter formatting, change the foreground color of these text column same as background color and apply to the totals only, so total will be hidden with same foreground and background color.

[J_L]

@parry2k mate, you're brilliant!!! Thanks so much, so far so good, I'll reach out if I need further help... 

[parry2k]

@J_L why you have text columns in the values section, put those on the rows and drill down with stepped layout off, but if you want text column on the values then we can control that will few tricks.

[J_L]

Hi @parry2k hope you had a good weekend and thanks for getting back to me, much appreciated! I've moved as many text items to rows as possible and am almost there however I do need a few text columns on the values (without being included in the subtotal) - what are these tricks?

[parry2k · Accepted solution]

@J_L you can easily achieve this with matrix visual, add date, day, an item on rows and turn off step layout on format page, add column that contains sold/unsold on columns or create two measures that does the sum for sold and unsold.

You can turn off the row-level total for everything except date and everything should work as expected and no fancy work required.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[J_L]

Thanks @parry2k for the quick response. You might have solved this for me!! I'm trying now with my actual data but one thing I've not been able to do so far is remove the subtotals for some of the columns that I don't want to be "subtotaled" the subtotal applies to all the columns, even text columns, which come as First....

Any further help will be greatly appreciated.