Summing values differently in same column based on name

[RandayOr]

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 in to indicate if the name is not equal to Earnings to perform the formula one way but if it is equal to earnings to instead add 2 other measurements. I confirmed the formula works perfectly for everything that does not have the name "Earnings" and that the measurements by themselves produce the desired results, but for some reason when the formula displays Earnings it does not add the 2 measures but instead operates as if it's not registering the name is Earnings. Any help would be appreciated. 

if(SELECTEDVALUE(Account[Name])<>"Earnings",

    calculate(

        sum(RawData[Amount]),

        CONTAINSSTRINGEXACT(RawData[Level_8_Consol],"+"),

            USERELATIONSHIP(RawData[Level_8],Account[Name])

        )

    -calculate(

        sum(RawData[Amount]),

        CONTAINSSTRINGEXACT(RawData[Level_8_Consol],"-"),

            USERELATIONSHIP(RawData[Level_8],Account[Name])

            ),Measure_Table[Measure1]+Measure_Table[Measure2]      

)

[hnguy71]

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]

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]

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 · Accepted solution]

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.