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 r...
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,

     

bajimmy1983's avatar
bajimmy1983
Advocate V
9 years ago

Hello Sean, good morning. 

 

Sorry, but as I am a Newbie right now, Filter Context is a little difficult to me right now. I am studing almost every day and trying to apply it as much as possible. 

 

So, I have applied both Measures (with ALL and ALLSELECTED), but again we could not reach desire outcome. See result in Figure 1 and then what I need in Figure 2 (as examples).

 

Obs: Measures in Column C are just for DAX calculation and will not sit in PivoTable. Just to ilustrate to you.

 

WRONGRIGHT

Thnaks again and have a nice day,

 

Sean's avatar
Sean
Community Champion
9 years ago

bajimmy1983

Post sample data in format that's easy to copy and paste into PBI (not a picture) of how you data is set up! :smileyhappy:

Not the pivot table and final result - just the data!

  • bajimmy1983's avatar
    bajimmy1983
    Advocate V
    9 years ago

    Hi Sean, 

     

    Please follow the link to download Sample Data zip file. I think you will have just to change Power Pivot connection paths in order to work in your machine.

     

    • File where all measures is: Dashboard_Pipeline_2017_MO_PP.xlsx;
    • Measure I am talking about ("GT. OBJ. BIL. EUR") is present in "phasing" Table in Data Model; 
    • "GT. OBJ. BIL. BRL:=" is just converting from EUR to BRL; 
    • PivotTable example is located in "PT_3_FILTER_GT. OBJ. BIL." worksheet; 
    • Column "D" from PivotTable is showing the wrong outcome. I need all lines showing 38.456.000

    Thanks once again Sean,

    • Sean's avatar
      Sean
      Community Champion
      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
        Advocate V
        9 years ago

        Sean, Greg_Deckler,  Mi2n and Community. THANK YOU SO MUCH for your patience :smileyvery-happy: :smileyvery-happy:

         

        This is a wonderful platform to ask for help, share what you have learned and learn again!!

         

        I am learning a lot here, with books and Youtube.

         

        Thank you so much again for your time and commitment!