Grand totals not matching with total row values .

[sruapp]

Problem:  Given the calc-group logic below, how should I implement the two gated outputs so that both row values and grand totals match the expected results?
Context & Grain:

• Visual/table grain: (Drop Window, Ship Zone, Item Variant).
• Drop Window uses season code + 2-digit year (e.g., SP 26, SU 27).
• LY mapping rule: For a row SS YY, its “LY units” comes from SS (YY-1) with the same Ship Zone + Item Variant. If no prior-year match exists, LY = 0.
• Repeat Style (LFL): “Yes” if the Item Variant appears in more than one distinct year; otherwise “No”. (Already available in the model as a column.)

Current Calculation Group (used today)
Calculation Group: 'TY vs LY (Calendar)'[TY vs LY]
TY (item)
=SELECTEDMEASURE()
LY (item) — season-aware with calendar fallbacks
VAR _seasonIsFiltered =
ISCROSSFILTERED('Global Buy Season') ||
ISFILTERED('Global Buy Season'[Global Buy Season]) ||
ISFILTERED('Global Buy Season'[Global Buy Season Year Full]) ||
ISFILTERED('Global Buy Season'[Global Buy Year]) ||
ISFILTERED('Global Buy Season'[Year & Season No])

VAR _seasonKeys =
SELECTCOLUMNS(
VALUES('Global Buy Season'[Year & Season No]),
"SeasonNo", 'Global Buy Season'[Year & Season No]
)
RETURN
SWITCH(
TRUE(),
// If Season is filtered — use SeasonNo-10
_seasonIsFiltered,
SUMX(
_seasonKeys,
VAR _curr = [SeasonNo]
VAR _ly = _curr - 10
RETURN
CALCULATE(
SELECTEDMEASURE(),
REMOVEFILTERS('Global Buy Season'),
'Global Buy Season'[Year & Season No] = _ly
)
),
// Else fallback to Calendar Date
NOT ISBLANK(MAX('Calendar'[Calendar Date])),
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR('Calendar'[Calendar Date])
),
// Else fallback to Year
NOT ISBLANK(MAX('Calendar'[Year])),
CALCULATE(
SELECTEDMEASURE(),
'Calendar'[Year] = MAX('Calendar'[Year]) - 1
),
// Else blank
BLANK()
)

Measures currently using the calc group
Units = CALCULATE([PO Qty], 'TY vs LY (Calendar)'[TY vs LY] = "TY")
LY Units = CALCULATE([PO Qty], 'TY vs LY (Calendar)'[TY vs LY] = "LY")

What I need help with
Create two gated outputs so that rows AND grand totals reflect the gate:
1.TY units with LY&TY>0 & LFL="Yes" → keep TY only if TY>0 AND LY>0 AND Repeat Style="Yes"; else 0.
2.LY units with LY&TY>0 & LFL="Yes" → keep LY only if TY>0 AND LY>0 AND Repeat Style="Yes"; else 0.

Grand Totals must equal the post-gate sums (not raw TY/LY). For the sample below, expected totals are TY=9,550 and LY=9,350.

Attached the data

Drop Window	Ship Zone	Item Variant	Units	LY Units	Repeat Style	TY Units (LY&TY>0 & LFL="Yes")	LY Units (LY&TY>0 & LFL="Yes")
SP 26	AMER	MX3D90ALFA	1000	0	Yes	0	0
SP 26	AMER	MX3D92BGHA	1000	0	Yes	0	0
SP 26	APAC	MX3D90ALFA	1400	0	Yes	0	0
SP 26	EMEA	MX3D90ALFA	800	0	Yes	0	0
SP 26	EMEA	P77Q9ZLUMA	2600	0	Yes	0	0
SP 26	Region1	Q4NOVA77X	500	0	Yes	0	0
SU 26	AMER	MX3D90ALFA	1450	0	Yes	0	0
SU 26	AMER	P77Q9ZLUMA	620	0	Yes	0	0
SU 26	APAC	MX3D90ALFA	580	0	Yes	0	0
SU 26	APAC	P77Q9ZLUMA	350	0	Yes	0	0
SU 26	EMEA	Q4NOVA77X	900	0	Yes	0	0
SP 27	AMER	MX3D90ALFA	950	1000	Yes	950	1000
SP 27	AMER	MX3D92BGHA	0	1000	Yes	0	0
SP 27	AMER	N3BETA55	700	600	No	0	0
SP 27	APAC	MX3D90ALFA	1200	1400	Yes	1200	1400
SP 27	EMEA	MX3D90ALFA	900	800	Yes	900	800
SP 27	EMEA	P77Q9ZLUMA	3100	2600	Yes	3100	2600
SP 27	Region1	Q4NOVA77X	0	500	Yes	0	0
SP 27	Region1	Z9OMEGA12	420	200	No	0	0
SU 27	AMER	MX3D90ALFA	1300	1450	Yes	1300	1450
SU 27	AMER	P77Q9ZLUMA	700	620	Yes	700	620
SU 27	APAC	MX3D90ALFA	600	580	Yes	600	580
SU 27	APAC	P77Q9ZLUMA	0	350	Yes	0	0
SU 27	APAC	R4KAPPA77	300	100	No	0	0
SU 27	EMEA	Q4NOVA77X	800	900	Yes	800	900
SU 27	EMEA	V5TTR993K	1050	0	No	0	0
Totals			23,220	12,100		9,550	9,350

[v-pgoloju]

Hi @sruapp,

Just wanted to follow up to check whether you’ve reached out to the Partners group for help. If yes, could you please share the status? This will help other community members who might be facing the same issue.

Thanks & Rehards,

Prasanna Kumar

[v-pgoloju]

Hi @sruapp,

Thanks for the update. Just following up to check if you’ve found any alternative solution. Please share it here so that others in the community facing the same issue can find it easily. 

If the issue still persists on your end, we recommend reaching out to our Power BI certified solution partners. These are highly experienced professionals who can provide in-depth technical assistance and offer tailored solutions based on your specific needs. You can explore the list of trusted partners here:

Power BI Partners | Microsoft Power Platform

You’re always welcome to continue engaging with the community as well,
Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread.
we truly appreciate your active participation in the Microsoft Fabric Community.

 

Thanks & Regards,

Prasanna Kumar

[v-pgoloju]

Hi @sruapp,

Just following up to see if the Response provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[sruapp]

@v-pgoloju This solution has not worked for us. Thanks for helping.

[v-pgoloju]

Hi @sruapp,

Could you please try below DAX measures, Before using this dax please define the gating logic as Keep a value only if TY > 0, LY > 0, and Repeat Style = "Yes", otherwise return 0.

TY Units  = SUMX('YourTable',
    VAR _ty = [Units]
    VAR _ly = [LY Units]
    VAR _repeat = 'YourTable'[Repeat Style]
    RETURN
        IF(_ty > 0 && _ly > 0 && _repeat = "Yes", _ty, 0))



LY Units = SUMX('YourTable',
    VAR _ty = [Units]
    VAR _ly = [LY Units]
    VAR _repeat = 'YourTable'[Repeat Style]
    RETURN
        IF(_ty > 0 && _ly > 0 && _repeat = "Yes", _ly, 0))

If the issue continues, please feel free to reach out to us  we’re here to help.

Thanks & Regards,

Prasanna Kumar

[v-pgoloju]

Hi @sruapp,

Just following up to check if the solution provided by the community member @Poojara_D12  resolved the issue. If it hasn’t, please let us know so we can assist in finding a resolution.

Thanks & Regards,

Prasanna Kumar

[sruapp]

@v-pgoloju The solution has not solved the issue. Totals are still coming in incorrect. Only at item variant level, its fine since its mentioned in the calc. But when I add another dimension to the viz, the total still shows at the level i.e item variant mentioned in the calc. I need it to be dynamic to the dimensions added/removed on the viz

[Poojara_D12]

Hi @sruapp 

this is a common issue when you “gate” values row by row in DAX: the row logic works, but at total level you’ll often see pre-gate numbers unless you explicitly recompute totals with the gate conditions applied. To make both row and grand totals align, you can’t just wrap an IF around [Units] or [LY Units] — you need measures that recalculate over the full visible set and apply the gate inside the aggregation.

1. Gate conditions

• Keep TY only if: TY > 0, LY > 0, and Repeat Style = "Yes".

• Keep LY only if: TY > 0, LY > 0, and Repeat Style = "Yes".
  Else → 0.

2. Implement gated TY

TY Units (Gated) =
SUMX (
    VALUES ( 'YourTable'[Item Variant] ),
    VAR _ty = CALCULATE ( [Units] )
    VAR _ly = CALCULATE ( [LY Units] )
    VAR _repeat = SELECTEDVALUE ( 'YourTable'[Repeat Style] )
    RETURN
        IF ( _ty > 0 && _ly > 0 && _repeat = "Yes", _ty, 0 )
)

3. Implement gated LY

LY Units (Gated) =
SUMX (
    VALUES ( 'YourTable'[Item Variant] ),
    VAR _ty = CALCULATE ( [Units] )
    VAR _ly = CALCULATE ( [LY Units] )
    VAR _repeat = SELECTEDVALUE ( 'YourTable'[Repeat Style] )
    RETURN
        IF ( _ty > 0 && _ly > 0 && _repeat = "Yes", _ly, 0 )
)

Using SUMX(VALUES(...)) forces DAX to evaluate row by row across the current visual grain (Drop Window, Ship Zone, Item Variant), apply the gating test, and then sum only the kept values. That way the total line is not a separate “rolled up” calc of Units/LY Units but instead the post-gate sum of all valid rows. That’s why you’ll see your totals align with your expectation (TY=9,550, LY=9,350 in your sample). you need to aggregate with the gating logic applied per row using an iterator (SUMX over the grain), instead of trying to filter inside a simple IF around a measure — because totals in DAX don’t behave like row context unless you explicitly tell them to.

[v-pgoloju]

Hi @sruapp,

Apologies for the late response. I’m currently working on the query and will share an update here ASAP.

Thanks & Regards,

Prasanna Kumar

[v-pgoloju]

Hi @sruapp,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @FBergamaschi  for prompt and helpful response.

Could you please provide more detailed information about the expected output as suggested by the community member? This will help us assist you further.

Thanks & Regards,

Prasanna Kumar

Could you please share 

[sruapp]

Hello, I have replied below. Pls check. Thanks in advance!

[FBergamaschi]

Hi @sruapp,

can you please attach some images that explain your post? I enetered the data in Power BI but I am struggling to understand your output. An image will help, also of your data model.

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[sruapp]

@FBergamaschi  pls check my response below

[sruapp]

Hello,

Here is the expected output based on the data I have shared.

In the below table, if you see the totals for PO Qty and PO Qty Repeat Style are different. Since the item variant Q4NOVA77X and Panama do not come under repeat style, they should not be included in the repeat style totals.

Repeat Style (LFL): “Yes” if the Item Variant appears in more than one distinct year; otherwise “No”.

Expected Output:

Item Variant	Ship Zone	Drop Window	Repeat Style	PO Qty TY	PO QTY Repeat Style TY
Q4NOVA77X	USA	SP 26	Yes	16,068.00	16068
MX3D90ALFA	Netherlands	SP 26	Yes	12,148.00	12148
Q4NOVA77X	China	SP 26	Yes	4,704.00	4704
Q4NOVA77X	Canada	SP 26	Yes	3,396.00	3396
Q4NOVA77X	Panama	SP 26	No	392
Totals				36,708.00	36,316.00

Current Output (incorrect):

Here the totals of PO QTY and PO QTY Repeat Style are matching, but the Panama row is still considered under totals, which is wrong.

Item Variant	Ship Zone	Drop Window	Repeat Style	PO Qty TY	PO QTY Repeat Style TY
Q4NOVA77X	USA	SP 26	Yes	16,068.00	16068
MX3D90ALFA	Netherlands	SP 26	Yes	12,148.00	12148
Q4NOVA77X	China	SP 26	Yes	4,704.00	4704
Q4NOVA77X	Canada	SP 26	Yes	3,396.00	3396
Q4NOVA77X	Panama	SP 26	No	392
Totals				36,708.00	36,708.00

We need a solution to work this out. 

Our Existing logic (this is giving incorrect totals):

PO QTY Repeat Style TY 

VAR ExtendedTable =
ADDCOLUMNS(
VALUES('Table1'[Item variant]),
"QTY", CALCULATE(
[PO Qty],
'TY vs LY (Calendar)'[TY vs LY] = "TY",
'Table1'[Repeat Style] = "Yes"
),
"QTY_TY", CALCULATE([PO QTY TY], 'Table1'[Repeat Style] = "Yes"),
"QTY_LY", CALCULATE([PO QTY LY], 'Table1'[Repeat Style] = "Yes")
)

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

RETURN
Numerator