Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vjnvinod
Impactful Individual
Impactful Individual

change number to abs value using a filter condition

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?

 

 

 

Capture.PNG

1 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.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

View solution in original post

15 REPLIES 15
Tahreem24
Super User
Super User

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!
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
vjnvinod
Impactful Individual
Impactful Individual

@Tahreem24 

 

can you add one more condition into it

 

if its reverse, make my actuals negative

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.
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
vjnvinod
Impactful Individual
Impactful Individual

@Tahreem24 

 

i need one meaure for both the  condition

2nd condition is not working

 

 

vjnvinod
Impactful Individual
Impactful Individual

 

@Tahreem24 

 

its all totaling to 763, see below issue

 

Capture.PNG

 

 

@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!

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
vjnvinod
Impactful Individual
Impactful Individual

@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 checkCapture.PNG

 

vjnvinod
Impactful Individual
Impactful Individual

@Tahreem24 

@V-pazhen-msft 

 

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

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. 

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
vjnvinod
Impactful Individual
Impactful Individual

@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

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!

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
vjnvinod
Impactful Individual
Impactful Individual

@Tahreem24 

 

still doesn't helps

 

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

 

Capture.PNG

 

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.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
vjnvinod
Impactful Individual
Impactful Individual

@Tahreem24 

Super worked Like a charm

Hi @vjnvinod ,

 

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

Good Luck!

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.