Forum Discussion
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
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.
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 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.
