Change Sign of Values depending on Category

[JSiebrecht]

I have a table with deals. Some are done within the same country, some are sales between two countries.
Depending on the point of view, such a deal might be considdered an import or an export.
But if it is an export, I want to show the deal value with a negative sign in my table visual.

The user shall be free to choose from which country's point of view they want to look at the numbers.

Now I am struggeling reversing the sign of the "export" deal's volume.

Example:

I have several Countries in a list the user can chose their "perspecive" from. For the example, let's limit this to the Netherlands and Belgium for the sake of the argument.

I have a table of Deals with the original direction of the deal (From -> To) and in a seperate table the Volume of each Deal.

I use the construct of the CombiKey to select all the relevant Deals involving the respective country the user chose.

So if the user selects "Netherlands", they will filter the Deals 1, 2 and 3 as they involve the Netherlands. The other deals in the list do not involve the Netherlands, so they are not filtered.
With the Deal ID the Deals are linked to their Volumes.

Now the Calculation shall do
Total = Same Country + Import - Export

In my table visual, I want to depict the Exports' Value with a negative sign, so if you siimply sum up the column, you get the Total for the perspective of the Netherlands:

The problem is, I can't define a fix "import" or "export" direction, because it will always depend on the perspective the user chooses.

If the user chooses Belgium from the list, it will filter only the deals 2 and 3 in this example, as only these two involve Belgium.

What's more, the interpretation, what constitutes an import and what an export has reversed.
So it shall take the deals and depict them like this:

Now I am struggeling reversing the sign of the "export" deal's volume.

I recon it must be done in a measure as it is context specific depending on the user's selection of country perspective.

But I can't seem to figure out how to compare the From/To combination to the selection of the Country slicer.

Any idea?

As always THANK YOU VERY MUCH for your ideas and input!


Cheers,
Jan

[BA_Pete · Accepted solution]

Hi @JSiebrecht ,

I can't give you an exact solution due to limited time, but my initial train of thought would be a measure along the lines of:

_volumeSwitch =
IF(
  SELECTEDVALUE(T_COUNTRIES[CODE]) = T_DEALS[From],
  T_VOLUMES[Volume],
  - T_VOLUMES[Volume]
)

Obviously you'd probably need to add some MAX's in there so you weren't referring to an entire column etc., but I think this structure would work in principle.

Pete

[JSiebrecht]

Good morning @BA_Pete 

thanks for your input. I really appreciate it.

I had a similar idea as well but could not get it to work. But with you thinking in the similar direction I tried this:
WORKED - in a somewhat unexpected way though. 

Measure I placed in the T_DEALS table

ImportExport_Flag = if(SELECTEDVALUE(T_COUNTRIES[Code]) <> FIRSTNONBLANK(T_DEALS[To], true()), -1, 1)

This was supposed to "classify" whether a deal needs multiplying by -1 to reverse the sign.
In the T_VOLUMES I then did the math

ImportExport_Volume = [Volume] * 'T_DEALS'[ImportExport_Flag]

At first I thought it does not work since when I built a table visual with Deal_ID From To and [ImportExport_Flag]I got the full table of Deals with all deals having a -1 despite I selected only NL and Deals 1, 2, and 3 in the slicers.
But when I took the [ImportExport_Flag] out of the visual and put in [Volume] and [ImportExport_Volume ] it showed only these 3 deals and the correct signs.

So it seems I was already on the right track but needed some reassurance by a fellow brain thinking along.

THANKS @BA_Pete !