Forum Discussion
derekli1700
1 year agoHelper III
How to make a Subtotal AND Total Row in Matrix Table
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:
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:
My dummy data is here:
| Category | Year | Period | Sales |
| Store A | 2024 | 202401 | 21654850 |
| Store A | 2024 | 202402 | 24454794 |
| Store A | 2024 | 202403 | 25303708 |
| Store A | 2024 | 202404 | 24214995 |
| Store A | 2024 | 202405 | 26705763 |
| Store A | 2024 | 202406 | 24054574 |
| Store A | 2024 | 202407 | 25835439 |
| Store A | 2024 | 202408 | 25997661 |
| Store A | 2024 | 202409 | 24774811 |
| Store A | 2024 | 202410 | 26897274 |
| Store A | 2024 | 202411 | 26590633 |
| Store A | 2024 | 202412 | 22310498 |
| Store A | 2025 | 202501 | 22543248 |
| Store A | 2025 | 202502 | 24241016 |
| Store A | 2025 | 202503 | 25702096 |
| Store A | 2025 | 202504 | 23331425 |
| Store B | 2024 | 202401 | 1527909 |
| Store B | 2024 | 202402 | 1614903 |
| Store B | 2024 | 202403 | 1663319 |
| Store B | 2024 | 202404 | 1603406 |
| Store B | 2024 | 202405 | 1766365 |
| Store B | 2024 | 202406 | 1616860 |
| Store B | 2024 | 202407 | 1714951 |
| Store B | 2024 | 202408 | 1718927 |
| Store B | 2024 | 202409 | 1578354 |
| Store B | 2024 | 202410 | 1668060 |
| Store B | 2024 | 202411 | 1649960 |
| Store B | 2024 | 202412 | 1552421 |
| Store B | 2025 | 202501 | 1558566 |
| Store B | 2025 | 202502 | 1556610 |
| Store B | 2025 | 202503 | 1668536 |
| Store B | 2025 | 202504 | 1532355 |
| Store C | 2024 | 202401 | 25080057 |
| Store C | 2024 | 202402 | 28821437 |
| Store C | 2024 | 202403 | 28357159 |
| Store C | 2024 | 202404 | 26754085 |
| Store C | 2024 | 202405 | 29519980 |
| Store C | 2024 | 202406 | 26501797 |
| Store C | 2024 | 202407 | 28170034 |
| Store C | 2024 | 202408 | 27551194 |
| Store C | 2024 | 202409 | 25740311 |
| Store C | 2024 | 202410 | 27918134 |
| Store C | 2024 | 202411 | 27292948 |
| Store C | 2024 | 202412 | 23288717 |
| Store C | 2025 | 202501 | 23156969 |
| Store C | 2025 | 202502 | 24718520 |
| Store C | 2025 | 202503 | 24976676 |
| Store C | 2025 | 202504 | 23019550 |
| Store D | 2024 | 202401 | 10555771 |
| Store D | 2024 | 202402 | 12022103 |
| Store D | 2024 | 202403 | 12198370 |
| Store D | 2024 | 202404 | 11689560 |
| Store D | 2024 | 202405 | 12732420 |
| Store D | 2024 | 202406 | 11099988 |
| Store D | 2024 | 202407 | 11431851 |
| Store D | 2024 | 202408 | 11667733 |
| Store D | 2024 | 202409 | 10965156 |
| Store D | 2024 | 202410 | 12077511 |
| Store D | 2024 | 202411 | 11870556 |
| Store D | 2024 | 202412 | 9009225 |
| Store D | 2025 | 202501 | 8968049 |
| Store D | 2025 | 202502 | 9368320 |
| Store D | 2025 | 202503 | 10141526 |
| Store D | 2025 | 202504 | 9283788 |
| Store E | 2024 | 202401 | 23689309 |
| Store E | 2024 | 202402 | 25754894 |
| Store E | 2024 | 202403 | 26449458 |
| Store E | 2024 | 202404 | 24591649 |
| Store E | 2024 | 202405 | 27151724 |
| Store E | 2024 | 202406 | 24603628 |
| Store E | 2024 | 202407 | 25972234 |
| Store E | 2024 | 202408 | 26047223 |
| Store E | 2024 | 202409 | 24458858 |
| Store E | 2024 | 202410 | 25932266 |
| Store E | 2024 | 202411 | 25719640 |
| Store E | 2024 | 202412 | 23602756 |
| Store E | 2025 | 202501 | 23188803 |
| Store E | 2025 | 202502 | 23792390 |
| Store E | 2025 | 202503 | 25266772 |
| Store E | 2025 | 202504 | 22876630 |
| Store G | 2024 | 202401 | 13552469 |
| Store G | 2024 | 202402 | 15152753 |
| Store G | 2024 | 202403 | 15105367 |
| Store G | 2024 | 202404 | 13549784 |
| Store G | 2024 | 202405 | 15469660 |
| Store G | 2024 | 202406 | 13688120 |
| Store G | 2024 | 202407 | 14373242 |
| Store G | 2024 | 202408 | 14599562 |
| Store G | 2024 | 202409 | 13917120 |
| Store G | 2024 | 202410 | 14804365 |
| Store G | 2024 | 202411 | 14859189 |
| Store G | 2024 | 202412 | 13079195 |
| Store G | 2025 | 202501 | 13329501 |
| Store G | 2025 | 202502 | 14008729 |
| Store G | 2025 | 202503 | 14685837 |
| Store G | 2025 | 202504 | 12856680 |
| Store H | 2024 | 202401 | 20256 |
| Store H | 2024 | 202402 | 21127 |
| Store H | 2024 | 202403 | 22010 |
| Store H | 2024 | 202404 | 22201 |
| Store H | 2024 | 202405 | 22434 |
| Store H | 2024 | 202406 | 18778 |
| Store H | 2024 | 202407 | 18470 |
| Store H | 2024 | 202408 | 16571 |
| Store H | 2024 | 202409 | 19549 |
| Store H | 2024 | 202410 | 18309 |
| Store H | 2024 | 202411 | 19798 |
| Store H | 2024 | 202412 | 23500 |
| Store H | 2025 | 202501 | 27924 |
| Store H | 2025 | 202502 | 19662 |
| Store H | 2025 | 202503 | 26120 |
| Store H | 2025 | 202504 | 15289 |
| Store I | 2024 | 202401 | 3069509 |
| Store I | 2024 | 202402 | 3462441 |
| Store I | 2024 | 202403 | 3412587 |
| Store I | 2024 | 202404 | 3262995 |
| Store I | 2024 | 202405 | 3686692 |
| Store I | 2024 | 202406 | 3215469 |
| Store I | 2024 | 202407 | 3631924 |
| Store I | 2024 | 202408 | 3850183 |
| Store I | 2024 | 202409 | 3394423 |
| Store I | 2024 | 202410 | 3669426 |
| Store I | 2024 | 202411 | 3456063 |
| Store I | 2024 | 202412 | 2753371 |
| Store I | 2025 | 202501 | 2683399 |
| Store I | 2025 | 202502 | 2859389 |
| Store I | 2025 | 202503 | 3118445 |
| Store I | 2025 | 202504 | 2938562 |
| Store J | 2024 | 202401 | 42988378 |
| Store J | 2024 | 202402 | 48812588 |
| Store J | 2024 | 202403 | 48833942 |
| Store J | 2024 | 202404 | 46181620 |
| Store J | 2024 | 202405 | 50989814 |
| Store J | 2024 | 202406 | 45755463 |
| Store J | 2024 | 202407 | 48236513 |
| Store J | 2024 | 202408 | 47508764 |
| Store J | 2024 | 202409 | 45413949 |
| Store J | 2024 | 202410 | 49013462 |
| Store J | 2024 | 202411 | 48074080 |
| Store J | 2024 | 202412 | 41321402 |
| Store J | 2025 | 202501 | 41304564 |
| Store J | 2025 | 202502 | 44153544 |
| Store J | 2025 | 202503 | 45453269 |
| Store J | 2025 | 202504 | 42052856 |
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 thisCALCULATE(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])))
1 Reply
- SamWiseOwlSuper User
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 thisCALCULATE(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])))