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: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
derekli1700
Helper III
Helper III

How to make a Subtotal AND Total Row in Matrix Table

‎05-12-2025 01:22 AM

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-1747037601502.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-1747038005804.png

 

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
161 Views
0
1 ACCEPTED SOLUTION
SamWiseOwl
Super User
Super User

‎05-12-2025 03:57 AM

Hi @derekli1700 

 

Could you modify your code so it returns two rows in the total?

Sales YTD =
IF(
    ISINSCOPE('Test Table'[Category])
, --On the row do this
        CALCULATE(
            SUM('Test Table'[Sales]),
            DATESYTD('DateTable'[Date])
        )
,--on the total do this
      "Subtotal " &  CALCULATE(
            SUM('Test Table'[Sales]),
            DATESYTD('DateTable'[Date])
            ,'Test Table'[Category] IN {"Store J", "Store C", "Store E", "Store A", "Store G", "Store D"}
        )
        &
        UNICHAR(10)
        & "Total " &
                CALCULATE(
            SUM('Test Table'[Sales]),
            DATESYTD('DateTable'[Date])
        )
)
 
SamWiseOwl_0-1747047415473.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

View solution in original post

Message 2 of 2
106 Views
0
1 REPLY 1
SamWiseOwl
Super User
Super User

‎05-12-2025 03:57 AM

Hi @derekli1700 

 

Could you modify your code so it returns two rows in the total?

Sales YTD =
IF(
    ISINSCOPE('Test Table'[Category])
, --On the row do this
        CALCULATE(
            SUM('Test Table'[Sales]),
            DATESYTD('DateTable'[Date])
        )
,--on the total do this
      "Subtotal " &  CALCULATE(
            SUM('Test Table'[Sales]),
            DATESYTD('DateTable'[Date])
            ,'Test Table'[Category] IN {"Store J", "Store C", "Store E", "Store A", "Store G", "Store D"}
        )
        &
        UNICHAR(10)
        & "Total " &
                CALCULATE(
            SUM('Test Table'[Sales]),
            DATESYTD('DateTable'[Date])
        )
)
 
SamWiseOwl_0-1747047415473.png

 


If you are happy with this answer please mark as a solution for others to find !

Kudos are always appreciated! Check out our free Power BI video courses.

Message 2 of 2
107 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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

View All
Top Solution Authors
View All