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 ,

 

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.