Exclude internal Transactions

[eitanhcs]

Hello all,
i would appreciate your help in finding a solution for my "Exclude internal Transactions"

My Company has 4 Factories that have sales between them, based on the selected slicer choice i would like to see sales that does not include internal (Between selected in slicer) Sales

Example:

<a href="http://tinypic.com?ref=2ztkf3c" target="_blank"><img src="http://i64.tinypic.com/2ztkf3c.jpg" border="0" alt="Image and video hosting by TinyPic"></a>

I would like The pivot in The middle to show only Sales to C and D when i Choose A and B in the slicer
and not as it shows in the picture.

This is the expected Result:

<a href="http://tinypic.com?ref=zr8lw" target="_blank"><img src="http://i67.tinypic.com/zr8lw.jpg" border="0" alt="Image and video hosting by TinyPic"></a>

The solution should also apply for 50 factories and more then multiple selection
Any idea?
Thanks

 

[Ozeroth]

Hi eitanhcs,

A couple of ways I can think of (without introducing any further tables):

(assuming your table is called Sales and [Value Sum] is defined as SUM ( Sales[Value] ) ):

[B]Value Sum where Factory To is not one of selected Factory From[/B]
=
CALCULATE (
    [Value Sum],
    FILTER (
        VALUES ( Sales[Factory To] ),
        NOT (
            CONTAINS (
                ALLSELECTED ( Sales[Factory From] ),
                Sales[Factory From], Sales[Factory To]
            )
        )
    )
)

or if you are using Excel 2016 (looks like you are since your slicers have multi-select option)

[B]Value Sum where Factory To is not one of selected Factory From[/B]
=
CALCULATE (
    [Value Sum],
    EXCEPT ( VALUES ( Sales[Factory To] ), ALLSELECTED( Sales[Factory From] ) )
)

Regards,
Owen

 

[eitanhcs]

Thank you very much. i have tried the second formula and it works perfect as long as you manually choose the factory in the slicer

the problem is that if you choose different Slicer (One hierarchy up for example) the formula does not recognize that you choose the factories

for example: (i Choose Company CCC which have only factory C and D in it)

<a href="http://tinypic.com?ref=2z72790" target="_blank"><img src="http://i63.tinypic.com/2z72790.jpg" border="0" alt="Image and video hosting by TinyPic"></a>


as apposed to : (i did not choose a company and have selected factories C and D directly)
<a href="http://tinypic.com?ref=vec752" target="_blank"><img src="http://i64.tinypic.com/vec752.jpg" border="0" alt="Image and video hosting by TinyPic"></a>

is there a why to solve it?
thanks

 

[Ozeroth]

Hi again,

There are other (and possibly better) ways to handle it, but a quick fix that should work is change the measure to:

Value Sum where Factory To is not one of selected Factory From
=
CALCULATE (
    [Value Sum],
    EXCEPT (
        VALUES ( Sales[Factory To] ),
        CALCULATETABLE ( VALUES ( Sales[Factory From] ), ALLSELECTED () )
    )
)

 

[eitanhcs]

Hi Thanks,
it did not Work, Choosing the Company Slicer had no affect on the Measure 1 Result.
can you please suggest one of the other options?
many thanks

 

[Ozeroth]

Hi again,
No problem - I may have made some assumptions about tables in your data model that weren't quite correct.
Could you upload a sample model similar to the one your screenshots came from (sanitised data if necessary), just so I can see the data model tables and relationships.
I'm sure it will be a simple fix.

Regards,
Owen

 

[eitanhcs]

Thanks,
i am not sure how to attach a file, is it possible with this Forum?

my data base, in the example, is basically the 4 column table i attached above (in the second picture thread - on the left side), i just uploaded it from excel to PowerPivot and create the pivot table based on that PowerPivot Data

[ the real data base is much bigger with many relationships that is available between the variables like Factory/Company/Groups etc... but i think by solving this example i will be able to implement it on the big one]

thanks you very much for your assistance

 

[Ozeroth]

That's ok.
I don't believe you can attach directly in this forum, but I was thinking of putting on a cloud storage service and providing a link.

Anyway, I think this measure should work regardless of exact table structure
(replace Sales with the name of your fact table, and if Factory From is in a separate table, replace Sales[Factory From] with LookupTable[Factory From]):

Value Sum where Factory To is not one of selected Factory From =
CALCULATE (
    [Value Sum],
    EXCEPT (
        VALUES ( Sales[Factory To] ),
        CALCULATETABLE ( SUMMARIZE ( Sales, Sales[Factory From] ), ALLSELECTED () )
    )
)

Let me know how this goes

 

[eitanhcs]

Hi Owen, Thanks
it seems to be working

 

[Ozeroth]

That's good. I had a sinking feeling after posting that I may have not been thinking correctly about the relationships.
Glad it's working!