Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
derekli1700
Helper III
Helper III

How to have both Subtotal AND Total Row in Matrix Table (whilst hiding some rows)

‎05-12-2025 11:57 PM

Hi - i currently have a table where i show all my store's YTD and PYTD sales and the YOY% change.

Sales YTD = 
CALCULATE(
    SUM('Export'[Sales]),
    DATESYTD('DateTable'[Date])
)
Sales PYTD = 
CALCULATE(
    [Sales YTD],
    SAMEPERIODLASTYEAR('DateTable'[Date])
)
YoY % = 
DIVIDE([Sales YTD] - [Sales PYTD], [Sales PYTD])

 and This is my date table:

DateTable = 
ADDCOLUMNS (
    CALENDAR (DATE(2021, 1, 1), DATE(2025, 4, 30)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "YearMonth", FORMAT([Date], "YYYYMM")
)

 
Currently my table looks like this:

derekli1700_0-1747119299324.png

 

I was wondering if theres any way so that my table only shows specific stores (Store A,C,D,E,G,J) with:

- A subtotal row adding their sales +YOY% change

- total row showing the total sales and YOY% shift across all stores (A,B,C,D,E,F,G,H,I,J).
Was hoping it look something like the table below- ideally a table that shows everything exactly in this format:

derekli1700_1-1747119299498.png

I know theres ways to do a subtotal row but it still involves keeping all stores in review + the subtotal row moving up and down if you filter by name/sales ascending and descending order etc. So was wondering if theres a custom visual/workaround to make it look 100% exactly like that image above

 

My dummy data is here:

CategoryYearPeriodSales
Store A202420240121654850
Store A202420240224454794
Store A202420240325303708
Store A202420240424214995
Store A202420240526705763
Store A202420240624054574
Store A202420240725835439
Store A202420240825997661
Store A202420240924774811
Store A202420241026897274
Store A202420241126590633
Store A202420241222310498
Store A202520250122543248
Store A202520250224241016
Store A202520250325702096
Store A202520250423331425
Store B20242024011527909
Store B20242024021614903
Store B20242024031663319
Store B20242024041603406
Store B20242024051766365
Store B20242024061616860
Store B20242024071714951
Store B20242024081718927
Store B20242024091578354
Store B20242024101668060
Store B20242024111649960
Store B20242024121552421
Store B20252025011558566
Store B20252025021556610
Store B20252025031668536
Store B20252025041532355
Store C202420240125080057
Store C202420240228821437
Store C202420240328357159
Store C202420240426754085
Store C202420240529519980
Store C202420240626501797
Store C202420240728170034
Store C202420240827551194
Store C202420240925740311
Store C202420241027918134
Store C202420241127292948
Store C202420241223288717
Store C202520250123156969
Store C202520250224718520
Store C202520250324976676
Store C202520250423019550
Store D202420240110555771
Store D202420240212022103
Store D202420240312198370
Store D202420240411689560
Store D202420240512732420
Store D202420240611099988
Store D202420240711431851
Store D202420240811667733
Store D202420240910965156
Store D202420241012077511
Store D202420241111870556
Store D20242024129009225
Store D20252025018968049
Store D20252025029368320
Store D202520250310141526
Store D20252025049283788
Store E202420240123689309
Store E202420240225754894
Store E202420240326449458
Store E202420240424591649
Store E202420240527151724
Store E202420240624603628
Store E202420240725972234
Store E202420240826047223
Store E202420240924458858
Store E202420241025932266
Store E202420241125719640
Store E202420241223602756
Store E202520250123188803
Store E202520250223792390
Store E202520250325266772
Store E202520250422876630
Store G202420240113552469
Store G202420240215152753
Store G202420240315105367
Store G202420240413549784
Store G202420240515469660
Store G202420240613688120
Store G202420240714373242
Store G202420240814599562
Store G202420240913917120
Store G202420241014804365
Store G202420241114859189
Store G202420241213079195
Store G202520250113329501
Store G202520250214008729
Store G202520250314685837
Store G202520250412856680
Store H202420240120256
Store H202420240221127
Store H202420240322010
Store H202420240422201
Store H202420240522434
Store H202420240618778
Store H202420240718470
Store H202420240816571
Store H202420240919549
Store H202420241018309
Store H202420241119798
Store H202420241223500
Store H202520250127924
Store H202520250219662
Store H202520250326120
Store H202520250415289
Store I20242024013069509
Store I20242024023462441
Store I20242024033412587
Store I20242024043262995
Store I20242024053686692
Store I20242024063215469
Store I20242024073631924
Store I20242024083850183
Store I20242024093394423
Store I20242024103669426
Store I20242024113456063
Store I20242024122753371
Store I20252025012683399
Store I20252025022859389
Store I20252025033118445
Store I20252025042938562
Store J202420240142988378
Store J202420240248812588
Store J202420240348833942
Store J202420240446181620
Store J202420240550989814
Store J202420240645755463
Store J202420240748236513
Store J202420240847508764
Store J202420240945413949
Store J202420241049013462
Store J202420241148074080
Store J202420241241321402
Store J202520250141304564
Store J202520250244153544
Store J202520250345453269
Store J202520250442052856
Message 1 of 2
130 Views
0
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

‎05-13-2025 04:13 AM

Hi @derekli1700 

Here's the closest I could get to this visual appearance using a Matrix visual.

 

Sample PBIX is attached.

 

1. Add a Dummy table with a single column and single row, containing an arbitrary value. In my example it is a table with column Dummy[Dummy] with single value "Dummy".

2. Create a Matrix visual with Dummy[Dummy] and Sales[Category] on rows, plus your measures in Values.

3. Set the Layout Preset to Tabular.

4. Turn off Wrap text in Column Headers and Row Headers.

5. Turn off +/- buttons for Row Headers.

6. Create a calculation group with a single calculation item with this expression:

IF (
    ISINSCOPE ( Dummy[Dummy] ),
    SELECTEDMEASURE (),
    CALCULATE ( SELECTEDMEASURE(), REMOVEFILTERS ( Sales[Category] ) )
)

7. Apply this calculation item as a filter to the matrix.

8. Reduce the width of the Dummy column so that it is invisible.

9. Change the Row subtotal label for Category to "Subtotal":

OwenAuger_2-1747135029911.png

 

10. Filter Category as required.

OwenAuger_1-1747134884357.png

 

Is that the sort of thing you were looking for?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

Subtotal and total.pbix
Message 2 of 2
81 Views
1 REPLY 1
OwenAuger
Super User
Super User

‎05-13-2025 04:13 AM

Hi @derekli1700 

Here's the closest I could get to this visual appearance using a Matrix visual.

 

Sample PBIX is attached.

 

1. Add a Dummy table with a single column and single row, containing an arbitrary value. In my example it is a table with column Dummy[Dummy] with single value "Dummy".

2. Create a Matrix visual with Dummy[Dummy] and Sales[Category] on rows, plus your measures in Values.

3. Set the Layout Preset to Tabular.

4. Turn off Wrap text in Column Headers and Row Headers.

5. Turn off +/- buttons for Row Headers.

6. Create a calculation group with a single calculation item with this expression:

IF (
    ISINSCOPE ( Dummy[Dummy] ),
    SELECTEDMEASURE (),
    CALCULATE ( SELECTEDMEASURE(), REMOVEFILTERS ( Sales[Category] ) )
)

7. Apply this calculation item as a filter to the matrix.

8. Reduce the width of the Dummy column so that it is invisible.

9. Change the Row subtotal label for Category to "Subtotal":

OwenAuger_2-1747135029911.png

 

10. Filter Category as required.

OwenAuger_1-1747134884357.png

 

Is that the sort of thing you were looking for?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn
Subtotal and total.pbix
Message 2 of 2
82 Views

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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

View All