cancel
Showing results for
Did you mean:

## 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 PLAYER MONTH CLIENT Act Bil BRL Shr Bil Correct AFFIPERF 1.091.739 100,00% 49,00% JAN 241.157 22,09% 22,09% EMIRATES 132.263 54,85% 54,85% HYUNDAI - CAOA 50.471 20,93% 20,93% CAOA SUBARU 35.539 14,74% 14,74% GLOBO 20.182 8,37% 8,37% CLUB MED 1.702 0,71% 0,71% CETIP 1.000 0,41% 0,41% FEV 650.985 59,63% 59,63% EMIRATES 155.540 23,89% 23,89% GLOBO 152.464 23,42% 23,42% TIM 116.667 17,92% 17,92% CLUB MED 110.258 16,94% 16,94% FUTURA 83.306 12,80% 12,80% HYUNDAI - CAOA 23.000 3,53% 3,53% METRÔ RIO 8.750 1,34% 1,34% CETIP 1.000 0,15% 0,15% MAR 199.597 18,28% 18,28% TIM 83.333 41,75% 41,75% FUTURA 65.454 32,79% 32,79% EMIRATES 26.060 13,06% 13,06% HYUNDAI - CAOA 23.000 11,52% 11,52% METRÔ RIO 1.750 0,88% 0,88% SOCIALYSE 1.039.954 100,00% 46,68% JAN 420.489 40,43% 40,43% TIM 262.405 62,40% 62,40% DIA 55.626 13,23% 13,23% CAOA SUBARU 39.825 9,47% 9,47% HYUNDAI - CAOA 22.151 5,27% 5,27% HYUNDAI - HMB 15.940 3,79% 3,79% EMIRATES 9.811 2,33% 2,33% STARBUCKS 6.731 1,60% 1,60% FOX 5.000 1,19% 1,19% CLUB MED 3.000 0,71% 0,71% FEV 367.839 35,37% 35,37% TIM 162.438 44,16% 44,16% DIA 96.349 26,19% 26,19% CAOA SUBARU 30.100 8,18% 8,18% METRÔ RIO 28.412 7,72% 7,72% CLUB MED 23.726 6,45% 6,45% FUTURA 19.620 5,33% 5,33% EMIRATES 7.194 1,96% 1,96% MAR 251.625 24,20% 24,20% TIM 175.328 69,68% 69,68% DIA 27.800 11,05% 11,05% CAOA SUBARU 18.500 7,35% 7,35% (blank) 16.640 6,61% 6,61% EMIRATES 7.268 2,89% 2,89% METRÔ RIO 6.088 2,42% 2,42% MOBEXT 61.149 100,00% 2,74% JAN 32.597 53,31% 53,31% TIM 32.597 100,00% 100,00% FEV 28.552 46,69% 46,69% GLOBO 20.274 71,01% 71,01% EMIRATES 8.278 28,99% 28,99% ECSELIS 35.002 100,00% 1,57% JAN 1.002 2,86% 2,86% CETIP 1.002 100,00% 100,00% FEV 34.000 97,14% 97,14% METRÔ RIO 33.000 97,06% 97,06% CETIP 1.000 2,94% 2,94% Grand Total 2.227.844 100,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.

Thanks a lot in advance and best regards,

4 REPLIES 4
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
)
)
)```

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.

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

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

Second Scenario (EXPECTED) = Flexible Measure

Thanks again,

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

Thanks a lot and have a nice day.

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

Announcements

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

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

#### 2022 Monthly Feature Releases

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

Top Solution Authors
Top Kudoed Authors