Power BI Grand total not matching row totals DAX for season based logic

[sruapp]

Hi All,

We are working on creating measures for Like for Like (LFL) logic based on season.

Example: A style color is considered LFL if it is present in the same season in both LY and TY (e.g., Fall 25 and Fall 24).

We already have a calculation group – 'TY vs LY (Calendar)'[TY vs LY] – that works on a season basis. So TY would be Fall 25 and LY would be Fall 24.

However, the issue is that the grand totals do not match the sum of the row values. Below highlighted in red are issue areas

 

	Expected Output	Filtered to Fall 2025
Origin Region	Destination Region	Style Nbr	Style Color Nbr	PO QTY TY	PO QTY LY	Sale amount LFL LY	PO QTY LFL TY Style Color Nbr	PO QTY LFL LY Style Color Nbr
ISC	EMEA	Style2	Style2Color1	20776
Africa	ASIA	Style1	Style1Color1	294
Africa	NA	Style1	Style1Color1	2408
Africa	ASIA	Style1	Style1Color1	630
Africa	NA	Style1	Style1Color1	1200
ISC	EMEA	Style2	Style2Color1	3037	20776	140274.68	3037	20776
Africa	ASIA	Style1	Style1Color1	1176	294	2651.88	1176	294
Africa	NA	Style1	Style1Color1	4866	2408	20224.38	4866	2408
ISC	South America	Style1	Style1Color1	98
Africa	ASIA	Style1	Style1Color1		630
Africa	NA	Style1	Style1Color1		1200
Total				34485	25308	163150.94	9079	23478

	Existing output	Filtered to Fall 2025
Origin Region	Destination Region	Style Nbr	Style Color Nbr	PO QTY TY	PO QTY LY	Sale amount LFL LY	PO QTY LFL TY Style Color Nbr	PO QTY LFL LY Style Color Nbr
ISC	EMEA	Style2	Style2Color1	20776
Africa	ASIA	Style1	Style1Color1	294
Africa	NA	Style1	Style1Color1	2408
Africa	ASIA	Style1	Style1Color1	630
Africa	NA	Style1	Style1Color1	1200
ISC	EMEA	Style2	Style2Color1	3037	20776	140274.68	3037	20776
Africa	ASIA	Style1	Style1Color1	1176	294	2651.88	1176	294
Africa	NA	Style1	Style1Color1	4866	2408	20224.38	4866	2408
ISC	South America	Style1	Style1Color1	98
Africa	ASIA	Style1	Style1Color1		630
Africa	NA	Style1	Style1Color1		1200
Total				34485	25308	163150.94	34485	25308

Issue:

• Expected output: Grand totals should equal the sum of row-level values.

• Current output: Grand totals are not matching row-level totals.

Has anyone encountered a similar problem with season-based LFL logic in Power BI? Any suggestions on how to fix the total mismatch would be appreciated.

Logic Created:

PO QTY LFL LY Style Color =
VAR ExtendedTable =
ADDCOLUMNS(
VALUES('Purchase Order'[Style Color Nbr]),
"QTY", CALCULATE(
[PO Qty],
'TY vs LY (Calendar)'[TY vs LY] = "LY",
'Purchase Order'[LFL (Style Color)] = "Yes"
),
"QTY_TY", CALCULATE([PO QTY TY], 'Purchase Order'[LFL (Style Color)] = "Yes"),
"QTY_LY", CALCULATE([PO QTY LY], 'Purchase Order'[LFL (Style Color)] = "Yes")
)

VAR Numerator =
SUMX(
ExtendedTable,
IF([QTY_TY] > 0 && [QTY_LY] > 0, [QTY_LY], BLANK())
)

RETURN
Numerator

---

Logic for LY (calc item) in  'TY vs LY (Calendar)'[TY vs LY] (Calc Group):

VAR _seasonIsFiltered =
// TRUE if any filter on Season table is active
ISCROSSFILTERED('Buy Season')
|| ISFILTERED('Buy Season'[Buy Season]) //(Example: Fall)
|| ISFILTERED('Buy Season'[Buy Season Year Full]) //(Example: Fall_2025)
|| ISFILTERED('Buy Season'[Buy Year]) //(Example: 2025)
|| ISFILTERED('Buy Season'[Year & Season No]) //(Example: 20253)

VAR _seasonKeys =
SELECTCOLUMNS(
VALUES('Buy Season'[Year & Season No]),
"SeasonNo", 'Buy Season'[Year & Season No]
)

RETURN
SWITCH(
TRUE(),

// If season is filtered — use LY Season logic
_seasonIsFiltered,
SUMX(
_seasonKeys,
VAR _curr = [SeasonNo]
VAR _ly = _curr - 10
RETURN
CALCULATE(
SELECTEDMEASURE(),
REMOVEFILTERS('Buy Season'),
'Buy Season'[Year & Season No] = _ly
)
),

// Else fallback to calendar logic
NOT ISBLANK(MAX('Calendar'[Calendar Date])),
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR('Calendar'[Calendar Date])//(date format mm/dd/yyyy)
),

// If even Calendar Date isn't in context, fallback to Year
NOT ISBLANK(MAX('Calendar'[Year])),
CALCULATE(
SELECTEDMEASURE(),
'Calendar'[Year] = MAX('Calendar'[Year]) - 1
),

// Else return blank
BLANK()
)

[v-kpoloju-msft · Accepted solution]

Hi @sruapp,

Thank you for reaching out to the Microsoft Fabric Community Forum and sharing such a detailed breakdown that really helps. Also, thanks to @Greg_Deckler, for his inputs on this thread.

The behaviour you are seeing, where the grand total doesn’t equal the sum of row totals, isn’t a bug but how DAX naturally evaluates totals. Totals are calculated in a broader context than each row, which is why the result can differ.

You can correct this by adjusting the measure logic to calculate at the row (Style Colour) level using an iterator like SUMX(VALUES(…)). This forces Power BI to aggregate each row’s result before totalling, which aligns the grand total with the visible rows. Here’s a quick example of that pattern:

PO Qty LFL LY =
SUMX(
    VALUES('Purchase Order'[Style Color Nbr]),
    VAR _qtyTY = CALCULATE([PO Qty], 'TY vs LY (Calendar)'[TY vs LY] = "TY")
    VAR _qtyLY = CALCULATE([PO Qty], 'TY vs LY (Calendar)'[TY vs LY] = "LY")
    RETURN IF(_qtyTY > 0 && _qtyLY > 0, _qtyLY)
)

This should give you matching totals without needing any product change.

Hope this helps. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @sruapp,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @sruapp,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[v-kpoloju-msft]

Hi @sruapp,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @Greg_Deckler, for his inputs on this thread.

Has your issue been resolved? If the response provided by the community member @Greg_Deckler, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[sruapp]

@v-kpoloju-msft the solution has not worked for us. 

[v-kpoloju-msft]

Hi @sruapp,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @sruapp,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[Greg_Deckler]

@sruapp First, please vote for this idea: https://community.fabric.microsoft.com/t5/Fabric-Ideas/Matrix-Table-grand-totals-with-Measures/idi-p...

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8