Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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,
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:
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:
This measure should be flexible to understand it and then bring back the correct % in all levels again like below:
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
102 | |
78 | |
76 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |