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
ElectricalGuy
Regular Visitor

automatically subtract by an additinal letter

hey guys, i am able to design simple charts and visuals with power bi and at least i created my first measures. But at this point i can't go ahead. I'll try to explain my issues. The table contains many offers with the value "ID" that looks like A123456 and the monetery value. To account internal offers with the seperate departments some offer including an other offer. That means, that for example the monetery value from the offer A123456.W is a part of the value from the offer A123456.

 

ID

Monetary Value

New Value

A123456

100.000€

40.000€

A123456.W

60.000€

60.000€

 

So i need a measure that identifys the ID's with an additional ".W" and subtract the Value of the ".W" offer from the basic offer without an ".W" into a new column. The suffix is always the same ".W"

 

thank you very much in advance

nico

 

1 ACCEPTED SOLUTION
Anand24
Super User
Super User

Hi @ElectricalGuy ,
There are many methods to do this. Stating one here.
Create a calculated measure with below DAX:

New_ID = IF(RIGHT(MAX('Table'[ID]),2) = ".W",LEFT(MAX('Table'[ID]),7),MAX('Table'[ID]))
Result:
Anand24_0-1679578972580.png
PBI_SuperUser_Rank@1x.png  

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

Proud To Be a Super User !!!
LinkedIn

 

View solution in original post

3 REPLIES 3
Anand24
Super User
Super User

Hi @ElectricalGuy ,
There are many methods to do this. Stating one here.
Create a calculated measure with below DAX:

New_ID = IF(RIGHT(MAX('Table'[ID]),2) = ".W",LEFT(MAX('Table'[ID]),7),MAX('Table'[ID]))
Result:
Anand24_0-1679578972580.png
PBI_SuperUser_Rank@1x.png  

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

Proud To Be a Super User !!!
LinkedIn

 

Hi @Anand24 ,

 

thank you very much for your fast respond. Unfortunately the solution for my issue needs a further step. I need to subtract the value out of the row "Vorgangssumme" as you can see at the picture. The formula would be:

 

If a seconde id with ".W" exist than subtract the value of "Vorgangssumme" from the row with the ´".W" from the value from the row without the ".W", as you can see in the picture below. Otherwise show the value without the subtraction.

 

ElecticalGuy_0-1679584602160.png

 

I hope that my explanations is comprehensible.

 

Hi @ElectricalGuy ,
Can you please open a new query for the further step?
Reason I'm asking this is because others would search in Power BI community according to the original post the the solution would not be appropriate for the original query to them.

You can probably put the link to that post/query in the replies here.

Thanks in advance !

PBI_SuperUser_Rank@1x.png  

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!

Proud To Be a Super User !!!
LinkedIn

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.