Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bajimmy1983
Helper V
Helper V

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

 

Jaderson Almeida
Business Coordinator
2 ACCEPTED SOLUTIONS
Sean
Community Champion
Community Champion

@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

View solution in original post

@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_1Bill_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_2Bill_Cost_Margin_Share_2

Best regards,

 

Jaderson Almeida
Business Coordinator

View solution in original post

19 REPLIES 19
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
Sean
Community Champion
Community Champion

@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

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.

 

WRONGWRONGRIGHTRIGHT

Thnaks again and have a nice day,

 

Jaderson Almeida
Business Coordinator
Sean
Community Champion
Community Champion

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

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
Sean
Community Champion
Community Champion

@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

@Sean, How are you? I hope fine.

 

So, could you or anyone help me once again inside the same context, but different measure (using same sample file I have sent)?

 

When I create measure below, at first glance I got the correct outcome in ROWS, but not in GrandTotal row (0%). Also, as soon as I change something, first Row of PivotTable (in this case Affiperf) gets wrong percentage! I have tried other measures, but with no success. 

 

Share Bil:=IF (
    ISFILTERED ( client[CLIENTE] );
    DIVIDE (
        [Bil BRL];
        CALCULATE ( [Bil BRL]; ALLSELECTED ( client[CLIENTE] ) );
        0
    );
    IF (
        ISFILTERED ( 'date'[MONTH] );
        DIVIDE ( [Bil BRL]; CALCULATE ( [Bil BRL]; ALLSELECTED ( 'date'[MONTH] ) ); 0 );
        IF (
            ISFILTERED ( 'group'[GRUPO] );
            DIVIDE ( [Bil BRL]; CALCULATE ( [Bil BRL]; ALLSELECTED ( 'group'[GRUPO] ) ); 0 );
            IF (
                ISFILTERED ( pure_players[PURE PLAYER] );
                DIVIDE (
                    [Bil BRL];
                    CALCULATE ( [Bil BRL]; ALLSELECTED ( pure_players[PURE PLAYER] ) );
                    0
                )
            )
        )
    )
)

First Outcome (almost correct except GrandTotal showing 0%.):

 

PURE PLAYERBil BRLShare Bil
AFFIPERF4.374.36450,39%
SOCIALYSE4.131.08347,59%
MOBEXT148.9731,72%
ECSELIS26.6400,31%
Grand Total8.681.0600,00%

 

Second Outcome (after change something in PivotTable. eg. expand Pure Player rows😞

 

PURE PLAYERBil BRLShare Bil
AFFIPERF4.374.364100,00%
 3.704.59084,69%
 669.77415,31%
SOCIALYSE4.131.08347,59%
 3.050.60073,85%
 693.76516,79%
 321.6417,79%
 37.5500,91%
 27.5280,67%
MOBEXT148.9731,72%
 79.54753,40%
 69.42746,60%
ECSELIS26.6400,31%
 24.63892,48%
 2.0027,52%
Grand Total8.681.0600,00%

 

Thanks again in advance,

 

 

 

Jaderson Almeida
Business Coordinator

Hello Community,

Sorry to FUP, but can anyone help me? I am really trying to resolve by myself, but filter context it is a little challenge for an old Excel user, jejeje!
Jaderson Almeida
Business Coordinator
Sean
Community Champion
Community Champion

What is Bil BRL - I can't get the same totals for this Measure with the previous data set you had posted?

@Sean, sorry, I forgot to mention that [Bil BRL] is: 

 

Bil BRL :=
SUM ( pipe[INVESTIMENTO LIQUIDO BRL] )
Jaderson Almeida
Business Coordinator
Sean
Community Champion
Community Champion

I can't duplicate what you are doing with the data I have (I've selected all - removed all filters)

 

Go in Excel and use the built-in Calculations to figure out what you need exactly % Of Row Total,  % of Parent Row Total ect...

 

Results Excel 2.png

 

If you are lucky you'll need one of the first 3 - those exist in PBI! Smiley Happy

@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_1Bill_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_2Bill_Cost_Margin_Share_2

Best regards,

 

Jaderson Almeida
Business Coordinator

Hi Community. 

 

Could anyone help me to resolve this little problem with a specific measure? You can replicate What I have according to SampleFile.

 

I am almost there (target), but this measure is not returning correct percentage for PURE PLAYER level in the PivotTable.

 

Please see an example as follow below. Red means wrong value measure is returning. I suspect the problem is at the final part, but I confess I cannot see a solution rightnow.

 

PURE PLAYERMONTHCLIENTAct Bil BRLShr BilCorrect
AFFIPERF  1.091.739100,00%49,00%
 JAN 241.15722,09%22,09%
  EMIRATES132.26354,85%54,85%
  HYUNDAI - CAOA50.47120,93%20,93%
  CAOA SUBARU35.53914,74%14,74%
  GLOBO20.1828,37%8,37%
  CLUB MED1.7020,71%0,71%
  CETIP1.0000,41%0,41%
 FEV 650.98559,63%59,63%
  EMIRATES155.54023,89%23,89%
  GLOBO152.46423,42%23,42%
  TIM116.66717,92%17,92%
  CLUB MED110.25816,94%16,94%
  FUTURA83.30612,80%12,80%
  HYUNDAI - CAOA23.0003,53%3,53%
  METRÔ RIO8.7501,34%1,34%
  CETIP1.0000,15%0,15%
 MAR 199.59718,28%18,28%
  TIM83.33341,75%41,75%
  FUTURA65.45432,79%32,79%
  EMIRATES26.06013,06%13,06%
  HYUNDAI - CAOA23.00011,52%11,52%
  METRÔ RIO1.7500,88%0,88%
SOCIALYSE  1.039.954100,00%46,68%
 JAN 420.48940,43%40,43%
  TIM262.40562,40%62,40%
  DIA55.62613,23%13,23%
  CAOA SUBARU39.8259,47%9,47%
  HYUNDAI - CAOA22.1515,27%5,27%
  HYUNDAI - HMB15.9403,79%3,79%
  EMIRATES9.8112,33%2,33%
  STARBUCKS6.7311,60%1,60%
  FOX5.0001,19%1,19%
  CLUB MED3.0000,71%0,71%
 FEV 367.83935,37%35,37%
  TIM162.43844,16%44,16%
  DIA96.34926,19%26,19%
  CAOA SUBARU30.1008,18%8,18%
  METRÔ RIO28.4127,72%7,72%
  CLUB MED23.7266,45%6,45%
  FUTURA19.6205,33%5,33%
  EMIRATES7.1941,96%1,96%
 MAR 251.62524,20%24,20%
  TIM175.32869,68%69,68%
  DIA27.80011,05%11,05%
  CAOA SUBARU18.5007,35%7,35%
  (blank)16.6406,61%6,61%
  EMIRATES7.2682,89%2,89%
  METRÔ RIO6.0882,42%2,42%
MOBEXT  61.149100,00%2,74%
 JAN 32.59753,31%53,31%
  TIM32.597100,00%100,00%
 FEV 28.55246,69%46,69%
  GLOBO20.27471,01%71,01%
  EMIRATES8.27828,99%28,99%
ECSELIS  35.002100,00%1,57%
 JAN 1.0022,86%2,86%
  CETIP1.002100,00%100,00%
 FEV 34.00097,14%97,14%
  METRÔ RIO33.00097,06%97,06%
  CETIP1.0002,94%2,94%
Grand Total  2.227.844100,00%100,00%

 

Measure I am using:

 

Shr Bil:=IF (
    ISFILTERED ( Client[CLIENT] );
    DIVIDE (
        [Act Bil BRL];
        CALCULATE ( [Act Bil BRL]; ALLSELECTED ( Client[CLIENT] ) );
        0
    );
    IF (
        ISFILTERED ( 'date'[MONTH] );
        DIVIDE ( [Act Bil BRL]; CALCULATE ( [Act Bil BRL]; ALLSELECTED ( 'date'[MONTH] ) ); 0 );
        IF (
            ISFILTERED ( 'group'[GRUPO] );
            DIVIDE ( [Act Bil BRL]; CALCULATE ( [Act Bil BRL]; ALLSELECTED ( 'group'[GRUPO] ) ); 0 );
            IF (
                ISFILTERED ( PurePlayers[PURE PLAYER] );
                DIVIDE ( [Act Bil BRL]; [GT Bil BRL]; 0 );
                DIVIDE ( [Act Bil BRL]; [GT Bil BRL]; 0 )
            )
        )
    )
)

 Thanks a lot in advance,

Jaderson Almeida
Business Coordinator

Community, could you please try to help?

 

Using the same SampleFile I shared yesterday, I am sharing now three prints. Wrong view and Expected ones. I think this helps you helping me.

 

Note that when I select all values in "GRUPO" slicer all works, but when I need to filter a specific one (eg: HMG) then the problem occurs.

 

Shr_Wrong.PNG

 

Shr_Correct_1.PNG

 

Shr_Correct_2.PNG

 

Thnaks again in advance,

Jaderson Almeida
Business Coordinator

Hi, anyone could please help me?
Jaderson Almeida
Business Coordinator

Hi @Sean, how are you?

Do not worry about totals to be the same. Data set was updated. if you help me with that measure will fit in every data set, right?

Thanks a lot,
Jaderson Almeida
Business Coordinator

@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

What is the error that you are getting?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.