Forum Discussion

RandayOr's avatar
RandayOr
Frequent Visitor
3 years ago
Solved

Summing values differently in same column based on name

Hi All,    I'm currently having an issue with the below formula, it is working as expected with the exception of when I try to have it use a different formula for "Earnings". I put an if statement ...
Need Help
  • RandayOr's avatar
    RandayOr
    3 years ago

    Sorry for any confusion, when I wrote out the example instead of putting "FCC_Retained Earnings Current" I shortened it to Earnings, the formula itself actually shows 

    IF( Account <> "FCC_Retained Earnings Current", Plus - Minus, [Measure1] + [Measure2] ). 

     

    I ended up switching the Cross Filter Direction from the Accounts table to the RawData table to Single cross filter direction and it was able to start seeing the FCC_Retained Earnings Current but only if there was a static value instead of measurement in the if statement. Since this issue is different than what the post previously was I'll mark this one as solved and start a new thread/question. 

hnguy71's avatar
hnguy71
Super User
3 years ago

Hi RandayOr ,

 

Seems like your measure is more-or-less correct. I've just rebuilt it to have better readability:

VAR Account = SELECTEDVALUE(Account[Name])
VAR Plus = 
    CALCULATE( 
        SUM(RawData[Amount]), 
        CONTAINSSTRINGEXACT(RawData[Level_8_Consol],"+"), 
        USERELATIONSHIP(RawData[Level_8],Account[Name]) 
    )

VAR Minus =     
    CALCULATE( 
        SUM(RawData[Amount]), 
        CONTAINSSTRINGEXACT(RawData[Level_8_Consol],"-"), 
        USERELATIONSHIP(RawData[Level_8],Account[Name]) 
    )

RETURN

IF( Account <> "EARNING", Plus - Minus, [Measure1] + [Measure2] )


We'll need to see what's the value output from [Measure1] and [Measure2]. Can you share a screnshot, sample data, or sample file?

RandayOr's avatar
RandayOr
Frequent Visitor
3 years ago

Went ahead and reformatted the way you suggested to make sure nothing was missed but received the same results. Here's a screenshot of the result I'm getting (currently filtering for just the 1 name). In the example I changed the name to Earnings to make it appear more simple but the name showing in the screenshot is what Earnings is actually displayed as. Output below shows Measure 1 and 2 correctly but Earnings is still off (results are those of Plus - Minus) 

 

 

  • hnguy71's avatar
    hnguy71
    Super User
    3 years ago

    Hi RandayOr ,

     

    So your "Account Name" is a full string that should look to see if "EARNING" is exact within the string and this is likely why it's never going to work.

     

    Try this instead:

     

    VAR Account = MAX(Account[Name])
VAR isEarning = SEARCH("EARNING", Account, 1, 0) > 0
VAR Plus = 
    CALCULATE( 
        SUM(RawData[Amount]), 
        CONTAINSSTRINGEXACT(RawData[Level_8_Consol],"+"), 
        USERELATIONSHIP(RawData[Level_8],Account[Name]) 
    )

VAR Minus =     
    CALCULATE( 
        SUM(RawData[Amount]), 
        CONTAINSSTRINGEXACT(RawData[Level_8_Consol],"-"), 
        USERELATIONSHIP(RawData[Level_8],Account[Name]) 
    )

RETURN

IF( NOT(isEarning), Plus - Minus, [Measure1] + [Measure2] )

     

     

    • RandayOr's avatar
      RandayOr
      Frequent Visitor
      3 years ago

      Sorry for any confusion, when I wrote out the example instead of putting "FCC_Retained Earnings Current" I shortened it to Earnings, the formula itself actually shows 

      IF( Account <> "FCC_Retained Earnings Current", Plus - Minus, [Measure1] + [Measure2] ). 

       

      I ended up switching the Cross Filter Direction from the Accounts table to the RawData table to Single cross filter direction and it was able to start seeing the FCC_Retained Earnings Current but only if there was a static value instead of measurement in the if statement. Since this issue is different than what the post previously was I'll mark this one as solved and start a new thread/question.