How to give rules to a measure to either include or exclude text. on a numerical calculation

[LPEquestrian]

Hey Everyone, So i am looking at creating a table which includes the Average shipping invoiced cost, calculating the difference in what we actually charge our customers. the problem i cirrently have is a syntax error, i think its due to the fact some spaces have text where we use a certain courier. I.E DHL. 

DHL Difference = SUM('OPMC Revenue Reporting_2023'[Invoiced]) - SUM(Sheet1[DHL])

This is the code i have used for my measure, and what it does is take the Invoiced Price - Our charged priced.... trying to give us a difference so we know how much we gain/lose on shipping.

The issue is some of the countries involved only have Standard shipping and this is marked with STD Only.   I think where the calculation tried ...... £99.99 - STD Only = ?????? is why i have issues.

It there anyway to give a rule or filter to a measure to allow it basically to enter the value STD Only or DHL Only. depending on the column?

Thanks ! ,

Lewis 

[LPEquestrian]

Hi Nathaniel,

So from my Data sets, I have 2 sets , 1 which has "Date/Country/Invoiced Price/Shipments sent" and the second set which has "Country/DHL Price/STND Price"  These are the prices we charge to the countries.

So the columns featuring the text also feature a number which is a price... the meaning behind this is some countries we only offer 1 service... so for example Australia, We offer DHL Only... the 2 columns will represent that

What i am trying to achieve is, we take the value of Invoiced Price "Minus" the cheapest shipping option we offer.  This is then to be displayed in another coloumn in the table stating "Difference" which displays in a currency value,

Data Type would be Currency, but im assuming as there is text present i have lost the option to set a column as currency. as it "Errors" all my text data.

This is an example of what im trying to achieve. I just filled in a few to show an example of what id like to see happen. Essentially if text is present i want it to bypass and then run the calculation on the available numbers.... and to filter to choose smallest number if 2 are present.

  Hopes this answers what you've asked.

[Nathaniel_C · Accepted solution]

Hi @LPEquestrian ,
If I understand what the issue is, this may be one way to solve this.

Create two conditional columns in Power Query that essentially filter out the "DHL Only" or "Standard Only" and replace with a zero.  Then make those new columns decimal.  Then run a find and replace null with zero.
Then you can use the measure below to do the calcs. (If I have the logic reversed, you could use an extremely large number in Power Query like 10000000 rather than zero, or reverse the >)

Please let me know if you have any issues as to how to do these steps in Power Query. I have included a picture.

Difference =
IF (
    MAX ( Shipping[DHL Derived] ) > MAX ( Shipping[Standard Derived] ),
    MAX ( Shipping[Invoiced Average] ) - MAX ( Shipping[DHL Derived] ),
    MAX ( Shipping[Invoiced Average] ) - MAX ( Shipping[Standard Derived] )
)

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

@LPEquestrian 

[Nathaniel_C]

Hi @LPEquestrian ,

£99.99 - STD Only certainly won't work.  " this is marked with STD Only" - Where is this marked? Is this in a column? What else is in that column? What is the data type? Please give us more information, so that we may help you solve this.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel