Filter on a calculated measure

[Anonymous]

Hi all,

I've made a power pivot. See the picture below. I'm checking each article, what we have received from a distribution centre (DC) and what we have filled (EFC) and if we have made corrections (0318). After that i've got a measure, if we have to controle a location. If the sum of DC-EFC-0318=0, we have received the correct amount. If the sum 0, somewhere in the proces has been made a mistake. Long story short, I only would like to see my mistakes. For instance, the sentence with 400. If i apply a, is not equal to 0, filter on the column 'control?' - all the totals dissapear and i only have left -54, 54, -24, 24 etc. Has anybody an idea how i get a power pivot that only shows 
129391 - VG18A01 - 400
Totaal 400

Thank you in advance!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create two measures as below.

Result = 
IF(
    ISINSCOPE('Table'[Level2]),
    SUM('Table'[Value]),
    var dc =
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Level1]=MAX('Table'[Level1])&&
            'Table'[Level2]="DC"
        )
    )
    var efc =
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Level1]=MAX('Table'[Level1])&&
            'Table'[Level2]="EFC"
        )
    )
    var _0318 =
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Level1]=MAX('Table'[Level1])&&
            'Table'[Level2]="0318"
        )
    )
    return
    dc-efc-_0318
)

Visual Control = 
var dc =
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Level1]=MAX('Table'[Level1])&&
        'Table'[Level2]="DC"
    )
)
var efc =
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Level1]=MAX('Table'[Level1])&&
        'Table'[Level2]="EFC"
    )
)
var _0318 =
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Level1]=MAX('Table'[Level1])&&
        'Table'[Level2]="0318"
    )
)
return
IF(
    dc-efc-_0318=0,
    0,
    1
)

Finally you need to put 'Visual Control' in the visual level filter to display the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Allan, thank you. This works perfect!

[amitchandak]

@Anonymous , Not very clear. But you can use a measure or slicer in a new column

[Anonymous]

Hi @amitchandak ,

First of all, thank you for your help. To be more concrete: I've got a calculated measure that i'd like to use as a filter/slicer, but I can't find this function. The calculated measure within this case = Column B. 

For example: From article 102408, the distribution centre sent us 54 pieces and we have booked 54 pieces. Per saldo is it 0. I'd like to net see these sentences (0, -54, 54) in my power pivot. When I filter on 0, the data presents itselves as  in picture 2. I only would like to see, the mistakes, as shown in picture 3.

hopefully you can help me out!

.