Need help with measure

[InsightSeeker]

Hello - I need to create a measure as

If trans_status is Sales return 1 and If trans_status is Refund return -1 else 0.

Table

trans_status

Sales

Sales

Sales

Refund

Sales

Refund

[Jihwan_Kim · Accepted solution]

Hi, 

I assume the table contains more than one column, and I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result measure: = 
VAR _condition =
    HASONEVALUE ( 'Table'[trans_status] )
VAR _resultcondition =
    DIVIDE ( _condition, _condition )
RETURN
    _resultcondition
        * SWITCH ( SELECTEDVALUE ( 'Table'[trans_status] ), "Sales", 1, "Refund", -1, 0 )

[VahidDM]

Hi @InsightSeeker 

Try this measure:
Measure = 
SWITCH (MAX( 'Table'[trans_status]),
"Sales",1,
"Refund",-1,
0
)

if you want to show nothing in total section if using this measure:

Measure = 

IF (
HASONEVALUE ( 'Table'[trans_status] ),
SWITCH (MAX( 'Table'[trans_status]),
"Sales",1,
"Refund",-1,
0
)
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[InsightSeeker]

@VahidDM - These measure are not returning any result. Looking like something is missing.

[VahidDM · Accepted solution]

@InsightSeeker 

Yes, there was an issue in Refund section, rather than " i typed ).

Fixed here:
WITHOUT Total:

Measure=
IF (
    HASONEVALUE ( 'Table'[trans_status] ),
    SWITCH ( MAX ( 'Table'[trans_status] ), "Sales", 1, "Refund", -1, 0 )
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Jihwan_Kim · Accepted solution]

Hi, 

I assume the table contains more than one column, and I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result measure: = 
VAR _condition =
    HASONEVALUE ( 'Table'[trans_status] )
VAR _resultcondition =
    DIVIDE ( _condition, _condition )
RETURN
    _resultcondition
        * SWITCH ( SELECTEDVALUE ( 'Table'[trans_status] ), "Sales", 1, "Refund", -1, 0 )