Wrong total shown in PBI using SUMX

[MCacc]

I have this measure in PBI:

KPI logeffact =
var ALTN =
CALCULATE(
sum('PrinTable'[KPI1]),
'PrinTable'[DataType] = "F",
'PrinTable'[CheckFlag] = "Y"
)

var AltDelta =
CALCULATE(
sum('PrinTable'[KPI2]),
'PrinTable'[CheckFlag] = "Y"
)

var logeff =
VAR ThisMonMth = MAX('Date Table'[YearMonth])
RETURN
CALCULATE(
(sum('PrinTable'[KPI3]) - sum('PrinTable'[KPI4])) ,
FILTER(
'PrinTable',
'PrinTable'[CheckFlag] = "Y"
&&
'PrinTable'[YearMonth] <= ThisMonMth
)
)
+ CALCULATE(
sum('PrinTable'[KPI5]),
'PrinTable'[DataType]="F",
'PrinTable'[CheckFlag] = "Y",
'PrinTable'[YearMonth] > ThisMonMth
)

VAR _monthOfAnalysis = SELECTEDVALUE ( 'Date Table'[YearMonth] )

VAR firstMonthOfAnalysis =
INT ( LEFT ( _monthOfAnalysis, 4 ) & "01" )
VAR rifemval =
IF (
NOT ISBLANK( _monthOfAnalysis),
CALCULATE (
SUM ( 'PrinTable'[Rif EM Value] ),
FILTER (
'PrinTable',
'PrinTable'[YearMonth] >= firstMonthOfAnalysis &&
'PrinTable'[YearMonth] <= _monthOfAnalysis
)
)
)

return
IF(rifemval=BLANK(), ALTN+ logeff,AltDelta+logeff)

1. VAR _monthOfAnalysis = SELECTEDVALUE ( 'Date Table'[YearMonth] ) is a slicer from a table detached from the data model. 
2. the KPI is correct month by month. the month dimension comes from PrinTable --> YearMonth which values are formatted as "202503", "202504" and so on. 
3. the total is wrong. 

the total sum should be: 

-657,528

for each month I have the following values:

month	value
jan	-6,856
feb	-53,733
march	-75,051
apr	-100,295
may	-215,299
jun	-29,672
jul	-26,813
aug	-7,285
sep	-112,616
oct	-8,493
nov	-4,501
dic	-16,914

but the total shown in PBI is -616.78609

I'm actuallt trying to replicate a BO KPI which uses exactly the same logic of my PBI measure but what it does is a 
=Sum([KPI with the same logic and results as PBI]  ForEach([year / month]) ) 
and when seen in BO the total actually is 657,528
1. year / month is my 'PrinTable'[YearMonth] as explained above, and it's formatted in the same way (202503,202504,...,ecc)

2. in BO _monthOfAnalysis  is a user prompt, but the behaviour is the same that is in action in PBI

This KPI in BO, like in my PBI tablix, is used with a dimension "Country" but not with any temporal dimension (nor yearmonth from PrinTable or _monthOfAnalysis)

this is what I did in PBI:

measure for Total =

SUMX(

    VALUES('Report With Graphs'[YearMonth]),   -- iterate over each month

        CALCULATE(

            [original measure as shown above]

        )

)

Please help me undertand how to retrieve the right total!

thank you!!

[v-kpoloju-msft · Accepted solution]

Hi @MCacc, 

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @Ritaf1983, @RonaldBalza-943, for those inputs on this thread. I reproduced the scenario, and it worked on my end. I used it as sample data and successfully implemented it.

outcome:

I am also including .pbix file for your better understanding, please have a look into it.

Hope this clears it up. 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 @MCacc,

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 @MCacc,

Just wanted to follow up. 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.

[Ritaf1983]

Hi @MCacc 
Please relate to the linked discussion:
https://community.fabric.microsoft.com/t5/Desktop/Sum-Measure-Calculations-in-Matrix-Totals/m-p/4863...

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[RonaldBalza-943]

This is the right idea, but you need to iterate over the same column used in your measure logic (PrinTable[YearMonth]) and ensure the calculation respects the slicer.

KPI logeffact =

SUMX(

    VALUES('PrinTable'[YearMonth]),   // iterate over each month in context

    CALCULATE(

        [KPI logeffact]               // your original measure

    )

)

[MCacc]

Yeah sorry, it a typo mistake. i used the same column of my measure logic.

i used your synatx and doesn't worl any other idea? do you think i have to use also the table from which i take my month of analysis slicer?

thanks