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

Percentage Sharing by level

Hi Community. 

 

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

 

What I need is to set up a PivotTable with some information levels as per below. I want to have a measure (Share Billing) that will represent share each level of PivotTable: 

 

 

This sharing must be dynamic, because in one moment I could have Company in first level, then Months, then Clients. In another moment I want to consume information putting Months first, then Company and so on.

 

Suppose I have a Pivot Table with four levels:

  • Level 1- Companies (A, B, C and D); 
  • Level 2- Company Groups (A1, A2, A3, A4 and A5); 
  • Level 3- Months (Jan to Dez); 
  • Level 4- Clients (N...). 

 

In Level 1

Company A with 7.370.374 (54,45% of total); 

Company B with 5.964.428 (44,07% of total);

Company C with 140.695 (1,04% of total); 

Company D with 59.640 (0,44% of total).

TOTAL level 1 = 13.535.138 (100%)

 

Then

 

1- Company A with 7.370.374 (54,45% of 13.535.138);

    1.1 Group A1 with 5.562.487 (75,47% of 7.370.374); 

    1.1 Group A2 with 1.077.954 (14,63% of 7.370.374);
    1.1 Group A3 with 657.912 (8,93% of 7.370.374);
    1.1 Group A4 with 62.591 (0,85% of 7.370.374);
    1.1 Group A5 with 9.431 (0,13% of 7.370.374);

    TOTAL level 2 = 7.370.374 (100%)

 

Then

1- Company A with 7.370.374 (54,45% of 13.535.138);

    1.1 Group A1 with 5.562.487 (75,47% of 7.370.374); 

        1.2 JAN with 1.801.120 (32,38% of 5.562.487);

        1.2 FEV with 1.794.168 (32,25% of 5.562.487);
        1.2 MAR with 1.945.885 (34,98% of 5.562.487);
        1.2 ABR with 21.314 (0,38% of 5.562.487);

        TOTAL level 3 = 5.562.487 (100%)

 

And son...

 

 

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

In the pictures below, note that when I select all values in "GRUPO" slicer, everything works just fine, but when I need to filter a specific one (eg: HMG group) then the problem occurs.

 

Shr_Wrong.PNG

 

Shr_Correct_1.PNG

 

Shr_Correct_2.PNG

 

Thanks a lot in advance and best regards,

 

Jaderson Almeida
Business Coordinator
4 REPLIES 4
Sean
Community Champion
Community Champion

Shr Bill NEW =
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
        ),
        DIVIDE (
            [Act Bil BRL],
            CALCULATE ( [Act Bil BRL], ALLSELECTED ( PurePlayers[PURE PLAYER] ) ),
            0
        )
    )
)

EXCEL.png

EDIT: In case anyone is wondering why there are 3 Shr Bil Measures?

Its because the Shr Bil in the file was different that the one listed above in the original question which I named Shr Bil WEB.

@Sean thanks for your reply.

 

I could realize that your NEW measure is almost the same as mine, excluding last part of my measure (thanks again). That's ok. 

 

Now try to picture in a Measure as follow below:

 

First Scenario (Already working)First Scenario (Already working)

First Scenario (Already working ina PivotTable)First Scenario (Already working ina PivotTable)

Now, imagine I want to change some information (between levels), but have the same result in % with a different perspective:

 

Important: Note that I have removed field "GRUPO" from Row context and put it in a Slicer. Then I want to filter just one "GRUPO", in this case "HMG". When I do this, I get a wrong % in the first level

 

Second Scenario (NOT WORKING as spected)Second Scenario (NOT WORKING as spected)

This measure should be flexible to understand it and then bring back the correct % in all levels again like below:

 

Second Scenario (EXPECTED) = Flexible MeasureSecond Scenario (EXPECTED) = Flexible Measure

 

Thanks again,

 

Jaderson Almeida
Business Coordinator

@Sean, Good morning. Do you know something else I could do to resolve this? 

 

Thanks a lot and have a nice day.

Jaderson Almeida
Business Coordinator

@MattAllington. I know you are a super super super user and expert. If you have time, could you please try to help?

 

Thanks and best regards,

Jaderson Almeida
Business Coordinator

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.