Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter on a calculated measure

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!

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Allan, thank you. This works perfect!

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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!

       

      .