topic Re: Subtotal in Matrix visual dynamic calculation error in DAX Commands and Tips

Subtotal in Matrix visual dynamic calculation error

BHPler — Wed, 19 Mar 2025 09:41:43 GMT

Hello,

I inherited a report that faces a bug related to a DAX measure that's way over my knowledge. Safe to say AI and days of trying to troubleshoot it didn't help.

The measure tries to calculate in a matrix % differences vs periods and target based on Factory (Site) and Period Slicers and it looks like this:

Site	2412	2501	2502	2503	2504	2505	2506	2507	2508	2509	2510	2511	2512	vs Start Period	Target	vs Target
Site1	92%	91%	99%	98%	99%	98%	99%	99%	99%	98%	99%	99%	100%	8	98%	2
Site2	100%	100%	100%	100%	100%	99%	99%	99%	100%	100%	99%	99%	100%		98%	2
Site3	70%	96%	95%	95%	95%	97%	96%	93%	95%	95%	90%	94%	93%	23	98%	-5
Site4	98%	99%	100%	100%	100%	100%	98%	100%	100%	100%	97%	100%	98%		98%
Site5	97%	94%	93%	85%	89%	93%	100%	100%	97%	93%	89%	95%	97%		98%	-1
Site6	100%													-100
Site7		100%	95%	96%	95%	98%	97%	96%	96%	96%	95%	97%	94%	94	98%	-4
Total	93%	97%	97%	96%	96%	98%	98%	98%	98%	97%	95%	97%	97%	2	98%	-1

The issue is with the highlighted subtotal value from the "vs Start Period" column, it's a hit and miss mostly not calculating correctly for Subtotals, while it calculates correctly per row basis..

For example the KPI values in the two periods:

Start Period (2412) Average KPI: 92.84%
Main Period (2512) Average KPI: 97.00%
Difference: 4.17%, but it shows 2 while it should be 4. It's the same issue based on different period selection, but incosnistent, as sometime the difference is just 1%, but it can be -49%. I might add that the target for 2024 was 95% instead of 98%.

What I got from AI is that the subtotal for "vs Start Period" shows 2, which is likely because of how the CompareToDate calculation is structured in your Power BI measure. Specifically that the measure attempts to compute Reference - Compare1, which is the KPI difference. The subtotal calculation seems to involve dividing by SUM(Config[Site Count FirstTimePass]), which is 12.
If 4.17% * 100 = 4.17, then 4.17 / 12 ≈ 2, which explains why the subtotal shows 2.

Any attempt to restructure the measure, or change it results in totally differnt values as to the initial scope. I'm not sure what is happening or how to get the right results.

The measure:

Values FirstTimePass =
VAR CountCodes = SUM('Config'[Total Periods FirstTimePass])
VAR MainPeriod =
CONVERT(
MAXX(
ALLSELECTED(DateDim[DateCode]),
DateDim[DateCode]
),
STRING
)
VAR StartPeriod =
CONVERT(
MINX(
ALLSELECTED(DateDim[DateCode]),
DateDim[DateCode]
),
STRING
)
VAR Target = MainPeriod
VAR DisplayItem = SELECTEDVALUE('FirstTimePass Header Table'[Order])
VAR FirstCode =
CALCULATE(
SUM('FirstTimePass Header Table'[Order]),
'FirstTimePass Header Table'[Value] = StartPeriod
)
VAR LastCode =
CALCULATE(
SUM('FirstTimePass Header Table'[Order]),
'FirstTimePass Header Table'[Value] = MainPeriod
)
VAR FirstRank = IF(FirstCode = BLANK(), MIN('First Time Pass'[Date Rank]), FirstCode)
VAR LastRank = IF(LastCode = BLANK(), MAX('First Time Pass'[Date Rank]), LastCode)
VAR Reference =
CALCULATE(
SUM('First Time Pass'[KPI]),
ALL('FirstTimePass Header Table'),
'FirstTimePass Header Table'[Value] = MainPeriod
)
VAR Compare1 =
CALCULATE(
SUM('First Time Pass'[KPI]),
ALL('FirstTimePass Header Table'),
'FirstTimePass Header Table'[Value] = StartPeriod
)
VAR Compare2 =
CALCULATE(
SUM('First Time Pass'[Target]),
ALL('FirstTimePass Header Table'),
'FirstTimePass Header Table'[Value] = Target
)
VAR CompareToDate =
VAR Subtraction1 = Reference - Compare1
RETURN
IF(
HASONEFILTER('First Time Pass'[Site]),
FORMAT(Subtraction1 * 100,"#"),
DIVIDE(
FORMAT(Subtraction1 * 100,"#"),
SUM(Config[Site Count FirstTimePass])
)
)
VAR CompareToTarget =
VAR Subtraction2 = Reference - Compare2
RETURN
IF(
HASONEFILTER('First Time Pass'[Site]),
FORMAT(Subtraction2 * 100,"#"),
FORMAT(
DIVIDE(
Subtraction2 * 100,
SUM(Config[Site Count FirstTimePass])
), "#"
)
)
VAR ValuesFirstTimePass =
IF(
HASONEFILTER('First Time Pass'[Site]),
SUM('First Time Pass'[KPI]),
DIVIDE(
SUM('First Time Pass'[KPI]),
DISTINCTCOUNT('First Time Pass'[Site])
)
)
VAR Actual_Target =
IF(
HASONEFILTER('OEE'[Site]),
FORMAT(Compare2,"0%"),
FORMAT(
CALCULATE(
AVERAGE('First Time Pass'[Target]),
ALL('FirstTimePass Header Table'),
'FirstTimePass Header Table'[Value] = Target
),
"0%"
)
)
// RETURN
VAR Test =
SWITCH(
TRUE(),
DisplayItem <= LastRank && DisplayItem >= FirstRank, ValuesFirstTimePass,
DisplayItem = CountCodes + 1, IF(CompareToDate=BLANK(),BLANK(),FORMAT(CompareToDate,"#")),
DisplayItem = CountCodes + 2, Actual_Target,
DisplayItem = CountCodes + 3, IF(CompareToTarget=BLANK(),BLANK(),FORMAT(CompareToTarget,"#"))
)
RETURN
IF(Test = BLANK(), BLANK(), Test)

_________________________________________________________________________________

I also prepared the dummy data:

First Time Pass Table

Year Month Total Site Date KPI Target Date Rank
2024 12 0.97 Site6 2412 97.00% 95% 75
2024 12 0.699 Site3 2412 69.90% 95% 75
2024 12 0.9971 Site2 2412 99.71% 95% 75
2024 12 0.98 Site4 2412 98.00% 95% 75
2024 12 0.924 Site1 2412 92.40% 95% 75
2025 12 0.97 Site5 2512 97.00% 98% 87
2025 11 0.95 Site5 2511 95.00% 98% 86
2025 10 0.89 Site5 2510 89.00% 98% 85
2025 9 0.93 Site5 2509 93.00% 98% 84
2025 12 0.98 Site4 2512 98.00% 98% 87
2025 10 0.97 Site4 2510 97.00% 98% 85
2025 11 0.99 Site1 2511 99.00% 98% 86
2025 10 0.99 Site1 2510 99.00% 98% 85
2025 11 0.99 Site2 2511 99.00% 98% 86
2025 10 0.99 Site2 2510 99.00% 98% 85
2025 12 0.93 Site3 2512 93.00% 98% 87
2025 11 0.94 Site3 2511 94.00% 98% 86
2025 10 0.9 Site3 2510 90.00% 98% 85
2025 12 0.94 Site7 2512 94.00% 98% 87
2025 11 0.97 Site7 2511 97.00% 98% 86
2025 10 0.95 Site7 2510 95.00% 98% 85
2025 9 0.96 Site7 2509 96.00% 98% 84
2025 8 0.96 Site7 2508 96.00% 98% 83
2025 7 0.96 Site7 2507 96.00% 98% 82
2025 6 0.97 Site7 2506 97.00% 98% 81
2025 5 0.98 Site7 2505 98.00% 98% 80
2025 4 0.95 Site7 2504 95.00% 98% 79
2025 3 0.96 Site7 2503 96.00% 98% 78
2025 2 0.95 Site7 2502 95.00% 98% 77
2025 8 0.97 Site5 2508 97.00% 98% 83
2025 5 0.93 Site5 2505 93.00% 98% 80
2025 4 0.89 Site5 2504 89.00% 98% 79
2025 3 0.85 Site5 2503 85.00% 98% 78
2025 2 0.93 Site5 2502 93.00% 98% 77
2025 1 0.94 Site5 2501 94.00% 98% 76
2025 6 0.98 Site4 2506 98.00% 98% 81
2025 1 0.99 Site4 2501 99.00% 98% 76
2025 9 0.95 Site3 2509 95.00% 98% 84
2025 8 0.951 Site3 2508 95.10% 98% 83
2025 7 0.933 Site3 2507 93.30% 98% 82
2025 6 0.964 Site3 2506 96.40% 98% 81
2025 5 0.97 Site3 2505 97.00% 98% 80
2025 4 0.95 Site3 2504 95.00% 98% 79
2025 3 0.951 Site3 2503 95.10% 98% 78
2025 2 0.948 Site3 2502 94.80% 98% 77
2025 1 0.96 Site3 2501 96.00% 98% 76
2025 7 0.9938 Site2 2507 99.38% 98% 82
2025 6 0.9938 Site2 2506 99.38% 98% 81
2025 5 0.99 Site2 2505 99.00% 98% 80
2025 3 0.9991 Site2 2503 99.91% 98% 78
2025 2 0.9965 Site2 2502 99.65% 98% 77
2025 1 0.9977 Site2 2501 99.77% 98% 76
2025 9 0.98 Site1 2509 98.00% 98% 84
2025 8 0.9911 Site1 2508 99.11% 98% 83
2025 7 0.9922 Site1 2507 99.22% 98% 82
2025 6 0.9945 Site1 2506 99.45% 98% 81
2025 5 0.98 Site1 2505 98.00% 98% 80
2025 4 0.99 Site1 2504 99.00% 98% 79
2025 3 0.982 Site1 2503 98.20% 98% 78
2025 2 0.993 Site1 2502 99.30% 98% 77
2025 1 0.906 Site1 2501 90.60% 98% 76
2025 11 1 Site4 2511 100.00% 98% 86
2025 12 1 Site1 2512 100.00% 98% 87
2025 12 1 Site2 2512 100.00% 98% 87
2025 1 1 Site7 2501 100.00% 98% 76
2024 12 1 Site6 2412 100.00% 95% 75
2025 7 1 Site5 2507 100.00% 98% 82
2025 6 1 Site5 2506 100.00% 98% 81
2025 9 1 Site4 2509 100.00% 98% 84
2025 8 1 Site4 2508 100.00% 98% 83
2025 7 1 Site4 2507 100.00% 98% 82
2025 5 1 Site4 2505 100.00% 98% 80
2025 4 1 Site4 2504 100.00% 98% 79
2025 3 1 Site4 2503 100.00% 98% 78
2025 2 1 Site4 2502 100.00% 98% 77
2025 9 1 Site2 2509 100.00% 98% 84
2025 8 1 Site2 2508 100.00% 98% 83
2025 4 1 Site2 2504 100.00% 98% 79

Sample of DateDim Table

Date DateCode DateCodeAsText DateCodeRank
1/1/2024 0:00 2401 2401 109
1/2/2024 0:00 2401 2401 109
1/3/2024 0:00 2401 2401 109
1/4/2024 0:00 2401 2401 109

...

12/27/2025 0:00 2512 2512 132
12/28/2025 0:00 2512 2512 132
12/29/2025 0:00 2512 2512 132
12/30/2025 0:00 2512 2512 132
12/31/2025 0:00 2512 2512 132

Config table

Site Count FirstTimePass Total Periods FirstTimePass
12 87

FirstTimePass Header Table

Value Order
2401 64
2402 65
2403 66
2404 67
2405 68
2406 69
2407 70
2408 71
2409 72
2410 73
2411 74
2412 75
2501 76
2502 77
2503 78
2504 79
2505 80
2506 81
2507 82
2508 83
2509 84
2510 85
2511 86
2512 87
vs Start Period 88
Target 89
vs Target 90

Any help or feedback would be greatly appreciated! If there is an easier way of doing this I am also open to it!

Thank you!

Re: Subtotal in Matrix visual dynamic calculation error

bhanu_gautam — Wed, 19 Mar 2025 11:38:44 GMT

BHPler , Try using

Values FirstTimePass =
VAR CountCodes = SUM('Config'[Total Periods FirstTimePass])
VAR MainPeriod =
CONVERT(
MAXX(
ALLSELECTED(DateDim[DateCode]),
DateDim[DateCode]
),
STRING
)
VAR StartPeriod =
CONVERT(
MINX(
ALLSELECTED(DateDim[DateCode]),
DateDim[DateCode]
),
STRING
)
VAR Target = MainPeriod
VAR DisplayItem = SELECTEDVALUE('FirstTimePass Header Table'[Order])
VAR FirstCode =
CALCULATE(
SUM('FirstTimePass Header Table'[Order]),
'FirstTimePass Header Table'[Value] = StartPeriod
)
VAR LastCode =
CALCULATE(
SUM('FirstTimePass Header Table'[Order]),
'FirstTimePass Header Table'[Value] = MainPeriod
)
VAR FirstRank = IF(FirstCode = BLANK(), MIN('First Time Pass'[Date Rank]), FirstCode)
VAR LastRank = IF(LastCode = BLANK(), MAX('First Time Pass'[Date Rank]), LastCode)
VAR Reference =
CALCULATE(
SUM('First Time Pass'[KPI]),
ALL('FirstTimePass Header Table'),
'FirstTimePass Header Table'[Value] = MainPeriod
)
VAR Compare1 =
CALCULATE(
SUM('First Time Pass'[KPI]),
ALL('FirstTimePass Header Table'),
'FirstTimePass Header Table'[Value] = StartPeriod
)
VAR Compare2 =
CALCULATE(
SUM('First Time Pass'[Target]),
ALL('FirstTimePass Header Table'),
'FirstTimePass Header Table'[Value] = Target
)
VAR CompareToDate =
VAR Subtraction1 = Reference - Compare1
RETURN
IF(
HASONEFILTER('First Time Pass'[Site]),
FORMAT(Subtraction1 * 100, "#"),
FORMAT(
AVERAGEX(
VALUES('First Time Pass'[Site]),
CALCULATE(Reference - Compare1) * 100
),
"#"
)
)
VAR CompareToTarget =
VAR Subtraction2 = Reference - Compare2
RETURN
IF(
HASONEFILTER('First Time Pass'[Site]),
FORMAT(Subtraction2 * 100, "#"),
FORMAT(
AVERAGEX(
VALUES('First Time Pass'[Site]),
CALCULATE(Reference - Compare2) * 100
),
"#"
)
)
VAR ValuesFirstTimePass =
IF(
HASONEFILTER('First Time Pass'[Site]),
SUM('First Time Pass'[KPI]),
DIVIDE(
SUM('First Time Pass'[KPI]),
DISTINCTCOUNT('First Time Pass'[Site])
)
)
VAR Actual_Target =
IF(
HASONEFILTER('OEE'[Site]),
FORMAT(Compare2, "0%"),
FORMAT(
CALCULATE(
AVERAGE('First Time Pass'[Target]),
ALL('FirstTimePass Header Table'),
'FirstTimePass Header Table'[Value] = Target
),
"0%"
)
)
VAR Test =
SWITCH(
TRUE(),
DisplayItem <= LastRank && DisplayItem >= FirstRank, ValuesFirstTimePass,
DisplayItem = CountCodes + 1, IF(CompareToDate = BLANK(), BLANK(), FORMAT(CompareToDate, "#")),
DisplayItem = CountCodes + 2, Actual_Target,
DisplayItem = CountCodes + 3, IF(CompareToTarget = BLANK(), BLANK(), FORMAT(CompareToTarget, "#"))
)
RETURN
IF(Test = BLANK(), BLANK(), Test)

Re: Subtotal in Matrix visual dynamic calculation error

BHPler — Wed, 19 Mar 2025 14:48:21 GMT

Thank you your solution!

It seems that now the issue is that the values for the vs Start Period and vs Target are not displaying at all:

They work perfectly for the rows, but the subtotal is missing. I would have it to calculate the "ST" for those two columns as well, meaning that in the example above it would show the difference of 97% vs 95% (i.e. the value of 2 for vs Start Period - 2410 and 1 for the vs Target).

I use a Matrix visual and have no additional filters on the page or visual.

Re: Subtotal in Matrix visual dynamic calculation error

v-hashadapu — Fri, 09 May 2025 11:25:19 GMT

Hi BHPler , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.

Re: Subtotal in Matrix visual dynamic calculation error

v-hashadapu — Fri, 20 Jun 2025 08:57:12 GMT

Hi , Thank you for reaching out to the Microsoft Community Forum.

Based on your description, I took some sample data and worked out the solution.

Please refer attached .pbix file for reference and share your thoughts.

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

Re: Subtotal in Matrix visual dynamic calculation error

v-hashadapu — Thu, 15 May 2025 12:29:48 GMT

Hi BHPler , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!

Re: Subtotal in Matrix visual dynamic calculation error

v-hashadapu — Fri, 20 Jun 2025 08:58:56 GMT

Hi BHPler , We are closing this thread as we haven't heard from you in a while, according to our follow-up policy. If you have any more questions, please start a new thread on the Microsoft Fabric Community Forum. We will be happy to assist you! Thank you for being part of the community!