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,

     

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,