Forum Discussion

bajimmy1983's avatar
bajimmy1983
Advocate V
9 years ago
Solved

Using FILTER - same propagation need

Hi all, 

 

Could you please help me getting the outcome as follow below (picture)? Problem is I cannot set up a Calculated Field that will repeat Total Year Objective Billings in every PivotTable row.

 

I need to set up a pivot table that will calculate % between Cumulative Actual Billings vs Total Year Objective Billings, row by row in the PivotTable.

 

As soon as I have a Calculated Field showing Total Year Objective Billings (column E in my picture example) in each row of PivotTable I think I can deal with % calculation (using DIVIDE function for example).

 

I do not know how to create a Calculated Field (column E) to produce this result in PivotTable for every year I select (so dinamically).

Thanks again all of you in advance,

 

Need Help
  • Sean's avatar
    Sean
    9 years ago

    bajimmy1983

     

    Okay I think this is it :smileyhappy:

    GT. OBJ. BIL. EUR =
CALCULATE (
    SUM ( phasing[VALUE] ),
    FILTER ( ALLSELECTED ( phasing ), phasing[TITLE] = "OBJ. BILLINGS EUR" )
)

    Result in PBI

    And In Excel...

     

    Hope this helps! :smileyhappy:

  • bajimmy1983's avatar
    bajimmy1983
    9 years ago

    Sean,

     

    I think I got it resolved. I am attaching SampleFile_v2.

     

    Could you please check if Measures "Share Bil", "Share Cost" and "Share Margin Bil" are correct? In case you have a better solution I kindly ask you to share with us! :smileywink: :smileyhappy:

     

    Now I have this point... If you look at the PivotTable in worksheet "BREAK_DOWN_Client" you will find:

     

    Bill_Cost_Margin_Share_1

    If I filter "AFFIPERF" in "PURE PLAYER" PivotTable column note that Shares (Bill, Cost and Margin Bil) get 100% (columns G, J and M).

     

    Is there a way to show 50,39% for AFFIPERF even if it is filtered in PivotTable ROW or when it is filtered in Filter PivotTable section and avoid the picture below?

     

    Bill_Cost_Margin_Share_2

    Best regards,

     

19 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    I believe that you will need to use an ALLEXCEPT filter in your measure calculation so that you remove the matrix row context and replace it with a context of company.

    • bajimmy1983's avatar
      bajimmy1983
      Advocate V
      9 years ago

      Hi Greg_Deckler. 

       

      I think I did wrong with the following Measure trying to use ALLEXCEPT... Could you please try to exemplify?

       

      Note:

      • Where you see "PURE_PLAYER" understand "COMPANY NAME"; 
      • Also in my Measure you see ... "FILTER(phasing;phasing[TITLE]="OBJ. BILLINGS EUR")" just because in Table "phasing" I have one column called TITLE with two information: "OBJ. BILLINGS EUR" and "OBJ. MARGIN EUR". I think you can understand that besides my desired outcome I also need to SUM just "OBJ. BILLINGS EUR". 

       

      Measure I am using, but fail:

       


      Grand Total OBJ. BIL. EUR :=
CALCULATE (
    SUM ( phasing[VALUE] );
    ALLEXCEPT ( phasing; phasing[PURE_PLAYER] );
    FILTER ( phasing; phasing[TITLE] = "OBJ. BILLINGS EUR" )
)

      Best regards,

       

       

       

      • Mi2n's avatar
        Mi2n
        Microsoft Employee
        9 years ago

        What is the error that you are getting?