cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bajimmy1983 Member
Member

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).

 

Desired_Outcome_Pending.PNGI 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,

 

Jaderson Almeida
Business Coordinator
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Using FILTER - same propagation need

@bajimmy1983

 

Okay I think this is it Smiley Happy

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

Result in PBI

Results.png

And In Excel...

Results Excel.png

 

Hope this helps! Smiley Happy

bajimmy1983 Member
Member

Re: Using FILTER - same propagation need

@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! Smiley Wink Smiley Happy

 

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

 

Bill_Cost_Margin_Share_1.PNGBill_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.PNGBill_Cost_Margin_Share_2

Best regards,

 

Jaderson Almeida
Business Coordinator
19 REPLIES 19
Super User
Super User

Re: Using FILTER - same propagation need

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


bajimmy1983 Member
Member

Re: Using FILTER - same propagation need

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,

 

 

 

Jaderson Almeida
Business Coordinator
Mi2n Member
Member

Re: Using FILTER - same propagation need

What is the error that you are getting?

Super User
Super User

Re: Using FILTER - same propagation need

@bajimmy1983  How about this?

 

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

or 

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

Good Luck! Smiley Happy

bajimmy1983 Member
Member

Re: Using FILTER - same propagation need

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.

 

Wrong_Result.PNGWRONGDesire_Outcome.PNGRIGHT

Thnaks again and have a nice day,

 

Jaderson Almeida
Business Coordinator
Super User
Super User

Re: Using FILTER - same propagation need

@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! Smiley Happy

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

Highlighted
bajimmy1983 Member
Member

Re: Using FILTER - same propagation need

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,

Jaderson Almeida
Business Coordinator
Super User
Super User

Re: Using FILTER - same propagation need

@bajimmy1983

 

Okay I think this is it Smiley Happy

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

Result in PBI

Results.png

And In Excel...

Results Excel.png

 

Hope this helps! Smiley Happy

bajimmy1983 Member
Member

Re: Using FILTER - same propagation need

@Sean, @Greg_Deckler,  @Mi2n and Community. THANK YOU SO MUCH for your patience Smiley Very Happy Smiley Very 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! 

Jaderson Almeida
Business Coordinator