Forum Discussion

MCacc's avatar
MCacc
Icon for Helper IV rankHelper IV
10 months ago
Solved

Wrong total shown in PBI using SUMX

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:

monthvalue
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 PBIForEach([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!!

  • 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.

6 Replies

  • 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's avatar
      MCacc
      Icon for Helper IV rankHelper IV

      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

  • 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's avatar
      v-kpoloju-msft
      Icon for Community Support rankCommunity Support

      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's avatar
        v-kpoloju-msft
        Icon for Community Support rankCommunity Support

        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.