Forum Discussion

SamfarUK's avatar
SamfarUK
Icon for Helper I rankHelper I
1 year ago
Solved

Calculation is not producing the correct figure

I have a column in the data spreadsheet that = £288k The transformed data in the PBI = £288k  The measure calculation in the card I have created = £300k     I do not understand how t...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Just ignore this. I think I've figured out why.

     

    CALCULATE (
    SUM ( 'consolidated'[value] ),
    'Consolidated'[Financial] = "Commitment"
)

     

    This part of the formula

    'Consolidated'[Financial] = "Commitment"

    is internally translated to

     FILTER(ALL(Consolidated[Financial]), Consolidated[Financial] = "Commitment")

    which filters all rows where Financial is "Commitment" and applies this filter across the entire Consolidated[Financial] column. Therefore, unless the filter originates from a different column, the result remains consistent regardless of the value selected in Consolidated[Financial].

     

    Change it to

     

    CALCULATE (
    SUM ( 'consolidated'[value] ),
    KEEPFILTERS ( 'Consolidated'[Financial] = "Commitment" )
)

     

     

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

hi SamfarUK 

It seems you are aiming to calculate the sum of values where financial = commitment. In this case, the CALCULATE portion of your measure should do the trick. When using SUMX across the entire Consolidated table, the  CALCULATE expression will be evaluated for each row, and the results will then be summed up.

  • SamfarUK's avatar
    SamfarUK
    Icon for Helper I rankHelper I
    1 year ago

    hello, thanks for your response!

    that does work and now calculates the correct figure, however, it has now broken the slicer element on my page:

     

    whereas previously you can see the cards change in line with the slicer settings:

     

    further help will be much appreciated (i haven't been using PBI very long and am self-taught).

    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      1 year ago

      hi SamfarUK 

       

      How are those measures being calculated? Can you please post a workable sample data that is not an image? Make sure to include all the necessary columns as the ones in your slicer are simply the values and the PO Number. You may post al link to a sanitized copy of your pbix or an Excel file stored in the cloud.

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        Just ignore this. I think I've figured out why.

         

        CALCULATE (
    SUM ( 'consolidated'[value] ),
    'Consolidated'[Financial] = "Commitment"
)

         

        This part of the formula

        'Consolidated'[Financial] = "Commitment"

        is internally translated to

         FILTER(ALL(Consolidated[Financial]), Consolidated[Financial] = "Commitment")

        which filters all rows where Financial is "Commitment" and applies this filter across the entire Consolidated[Financial] column. Therefore, unless the filter originates from a different column, the result remains consistent regardless of the value selected in Consolidated[Financial].

         

        Change it to

         

        CALCULATE (
    SUM ( 'consolidated'[value] ),
    KEEPFILTERS ( 'Consolidated'[Financial] = "Commitment" )
)