Create Virtual Table and "Related" to use value from same fact table of filtered dataset

[Juju]

Hi,

I have two companies in the same fact table, which have intercompany sales.

To eliminate the IC margin in my report, I would like to replace the COGS value from the company selling to the end customer (Co.1) with the COGS value from the supplying company (Co.2).

The SO Number is the same for the sale from Co.2 => Co.1 => End customer

Probably not best practise, but I created a concatenated column using the product key, sales quantity and SO number so there is a relationship for the SKU sale in each company.

As I only want the sales numbers from Company 1, I have a slicer on the Company name.

I am trying to work out the DAX calculation to do this.

IC COGS = VAR Co2COGs =calculate( [COGS],
filter (ALL(sales), Sales[Company Key]=2))

And then can I create a Table for only Company 2,

and then use RELATED and Concate column to return Company 2 Cogs?

The desired end result below, and PBIX file attached.

Thanks in advance for your help, any suggestions or perhaps using ““Concat”” in the DAX to create the Concat column would be greatly appreciated!

Thanks

COGS Test.pbix (474.3 KB)

[EnterpriseDNA]

Bumping this post for more visibility.

[Melissa]

Hi @Juju,

Got two options for you to consider.
The most straight forward one is instead of doing it virtually, do a Self Merge on Sales, to retrieve the IC GOCS.

.

This reveals there’s an issue with your sample, these highlighted lines don’t match

.

Then it’s just a simple SUM over IC GOCS (note that the difference with the expected result will resolve once the sample data is amended)

But you can also solve it with DAX, note that depending on the context required in your report you might have to update the logic in this measure.

IC COGS v2 = 
VAR vTable =
    CALCULATETABLE( Sales,
        REMOVEFILTERS( Sales ),
        TREATAS( VALUES( Company[Company Key] ), Sales[Customer Key] ),
        VALUES( Sales[Concate] )
    )

RETURN
    SUMX( vTable, [COGS] )

Here’s your sample file:
IC COGS.pbix (64.7 KB)

I hope this is helpful.

[Juju]

Very helpful!! Thank you very much, hope you had a happy holiday! Much appreciated.

[EnterpriseDNA]

It’s great to know that you are making progress with your query @juju.

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey, We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

[EnterpriseDNA]

Hello @juju, just following up if the response from @Melissa help you solve your inquiry?

We’ve noticed that no response has been received from you since a few days ago. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[Juju]

Yes, thank you very much !