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,

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

    • bajimmy1983's avatar
      bajimmy1983
      Advocate V
      9 years ago

      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,

       

       

       

      • bajimmy1983's avatar
        bajimmy1983
        Advocate V
        9 years ago
        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!