change number to abs value using a filter condition

[vjnvinod]

Hi,

Can you help me with a measure,

basically i need to convert any negative numbers in my coloumn(ActualChrdHours) to positive number if my coloumn(ReverseVsMercurry) is "Not a reverse".

can you help?

[Tahreem24]

Hi @vjnvinod ,

Please give a try to below Measure:

Measure = ABS(CALCULATE(SUM('Table'[ActualChrdHour]),'Table'[ReverseVsMercurry]="Not a reverse"))

Please give Kudos and accept it as a solution if it helps you!

[vjnvinod]

@Tahreem24 

can you add one more condition into it

if its reverse, make my actuals negative

[Tahreem24]

Hi @vjnvinod ,

You can use the below Measure for your new requirement.

Measure 2 = if(sum('Table'[ActualChrdHour])<0,(ABS(CALCULATE(SUM('Table'[ActualChrdHour]),'Table'[ReverseVsMercurry]="Not a reverse"))),sum('Table'[ActualChrdHour]))

I expect Kudos to my previous answer 😊 and this answer as well. And Please Accept this as a solution.

[vjnvinod]

@Tahreem24 

i need one meaure for both the  condition

2nd condition is not working

[vjnvinod]

@Tahreem24 

its all totaling to 763, see below issue

[Tahreem24]

@vjnvinod ,

As per your very first message in this post you mentioned below statement:

"basically i need to convert any negative numbers in my coloumn(ActualChrdHours) to positive number if my coloumn(ReverseVsMercurry) is "Not a reverse".

Based on above requirement we requirement two fields i.e. ActualChrdHours and ReverseVsMercurry. So why you are using chargedHour field? And this ChargedHour is coming from table different than where these ActualChrdHours and ReverseVsMercury are coming. SO make the proper relationship between those two fields cause this is happening due to improper relationship. 

Thanks!

[vjnvinod]

@Tahreem24 

i think while  i type the table it picked it up, sorry for that,below is what i have tried, but some error, can you check

[vjnvinod]

@Tahreem24 

@V-pazhen-msft 

Can you please help me with the error on my measure?

[Tahreem24]

HI @vjnvinod ,

 Use the below meaure to fullfill your requirement:

Measure 2 = if(sum('Table'[ActualChrdHour])<0,(ABS(CALCULATE(SUM('Table'[ActualChrdHour]),'Table'[ReverseVsMercurry]="Not a reverse"))),sum('Table'[ActualChrdHour]))

I dont know why you are using two IF condition in your measure. 

[vjnvinod]

@Tahreem24 

Measure 2 = if(sum('Table'[ActualChrdHour])<0,(ABS(CALCULATE(SUM('Table'[ActualChrdHour]),'Table'[ReverseVsMercurry]="Not a reverse"))),sum('Table'[ActualChrdHour]))

I dont know why you are using two IF condition in your measure. 

that is because your measure above is meets only 1 condition &  doesn't meet the 2nd condition for instance

i need to convert my actual charged hour to negative, if 'Table'[ReverseVsMercurry]="Reverse Charge")

if(sum('Table'[ActualChrdHour])>0,(ABS(CALCULATE(SUM('Table'[ActualChrdHour])*-1,'Table'[ReverseVsMercurry]="Reverse Charge"))),sum('Table'[ActualChrdHour]))

how do you combine this 2 measures to 1?

hope it clarifies

[Tahreem24]

Hi @vjnvinod ,

Create 2 measures one for Reverse and another for Not a Reverse.

1. Reverse Charge=if(sum('Table'[ActualChrdHour])>0,(ABS(CALCULATE(SUM('Table'[ActualChrdHour])*-1,'Table'[ReverseVsMercurry]="Reverse Charge"))),sum('Table'[ActualChrdHour]))

2.  Not a Reverse = if(sum('Table'[ActualChrdHour])<0,(ABS(CALCULATE(SUM('Table'[ActualChrdHour]),'Table'[ReverseVsMercurry]="Not a reverse"))),sum('Table'[ActualChrdHour]))

Then use them into Switch statement like below by using third custom column:

Final Output Column=SWITCH('Table'[ReverseVsMercurry],"Not a reverse",[Not a Reverse],"Reverse Charge",[Reverse Charge])

Please Give KUDOS to this effort and accept this as a solution if it helps you!

[vjnvinod]

@Tahreem24 

still doesn't helps

Switch function doesn't reads my table coloumn, see below, hence the Switch logic doesn't works

[Tahreem24 · Accepted solution]

HI @vjnvinod ,

Your Switch column has created in different table than where yours Reverse and ActualChrdHours fields are coming. I suggest you to Right click on your DataDumpFull(2) Table at right side of Pane then click on create a New COlumn option.

[vjnvinod]

@Tahreem24 

Super worked Like a charm

[Tahreem24]

Hi @vjnvinod ,

WOW!!! Finally,  am really very happy that I helped you to solve this problem! 😊

Good Luck!