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 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 row.
I need to set up a pivot table that will calculate % between Cumulative Actual Billings vs Total Year Objective Billings, row by row in the PivotTable.
As soon as I have a Calculated Field showing Total Year Objective Billings (column E in my picture example) in each row of PivotTable I think I can deal with % calculation (using DIVIDE function for example).
Thanks again all of you in advance,
Solved! Go to Solution.
Okay I think this is it
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!
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!
Now I have this point... If you look at the PivotTable in worksheet "BREAK_DOWN_Client" you will find:
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?
Best regards,
I believe that you will need to use an ALLEXCEPT filter in your measure calculation so that you remove the matrix row context and replace it with a context of company.
Hi @Greg_Deckler.
I think I did wrong with the following Measure trying to use ALLEXCEPT... Could you please try to exemplify?
Note:
Measure I am using, but fail:
Grand Total OBJ. BIL. EUR := CALCULATE ( SUM ( phasing[VALUE] ); ALLEXCEPT ( phasing; phasing[PURE_PLAYER] ); FILTER ( phasing; phasing[TITLE] = "OBJ. BILLINGS EUR" ) )
Best regards,
@bajimmy1983 How about this?
Grand Total OBJ. BIL. EUR := CALCULATE ( SUM ( phasing[VALUE] ); ALL ( phasing[MONTH] ); FILTER ( phasing; phasing[TITLE] = "OBJ. BILLINGS EUR" ) )
or
Grand Total OBJ. BIL. EUR := CALCULATE ( SUM ( phasing[VALUE] ); ALLSELECTED ( phasing ); FILTER ( phasing; phasing[TITLE] = "OBJ. BILLINGS EUR" ) )
Good Luck!
Hello @Sean, good morning.
Sorry, but as I am a Newbie right now, Filter Context is a little difficult to me right now. I am studing almost every day and trying to apply it as much as possible.
So, I have applied both Measures (with ALL and ALLSELECTED), but again we could not reach desire outcome. See result in Figure 1 and then what I need in Figure 2 (as examples).
Obs: Measures in Column C are just for DAX calculation and will not sit in PivoTable. Just to ilustrate to you.
Thnaks again and have a nice day,
Post sample data in format that's easy to copy and paste into PBI (not a picture) of how you data is set up!
Not the pivot table and final result - just the data!
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.
Thanks once again @Sean,
Okay I think this is it
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!
@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 PLAYER | Bil BRL | Share Bil |
AFFIPERF | 4.374.364 | 50,39% |
SOCIALYSE | 4.131.083 | 47,59% |
MOBEXT | 148.973 | 1,72% |
ECSELIS | 26.640 | 0,31% |
Grand Total | 8.681.060 | 0,00% |
Second Outcome (after change something in PivotTable. eg. expand Pure Player rows😞
PURE PLAYER | Bil BRL | Share Bil |
AFFIPERF | 4.374.364 | 100,00% |
3.704.590 | 84,69% | |
669.774 | 15,31% | |
SOCIALYSE | 4.131.083 | 47,59% |
3.050.600 | 73,85% | |
693.765 | 16,79% | |
321.641 | 7,79% | |
37.550 | 0,91% | |
27.528 | 0,67% | |
MOBEXT | 148.973 | 1,72% |
79.547 | 53,40% | |
69.427 | 46,60% | |
ECSELIS | 26.640 | 0,31% |
24.638 | 92,48% | |
2.002 | 7,52% | |
Grand Total | 8.681.060 | 0,00% |
Thanks again in advance,
What is Bil BRL - I can't get the same totals for this Measure with the previous data set you had posted?
@Sean, sorry, I forgot to mention that [Bil BRL] is:
Bil BRL := SUM ( pipe[INVESTIMENTO LIQUIDO BRL] )
I can't duplicate what you are doing with the data I have (I've selected all - removed all filters)
Go in Excel and use the built-in Calculations to figure out what you need exactly % Of Row Total, % of Parent Row Total ect...
If you are lucky you'll need one of the first 3 - those exist in PBI!
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!
Now I have this point... If you look at the PivotTable in worksheet "BREAK_DOWN_Client" you will find:
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?
Best regards,
Hi Community.
Could anyone help me to resolve this little problem with a specific measure? You can replicate What I have according to SampleFile.
I am almost there (target), but this measure is not returning correct percentage for PURE PLAYER level in the PivotTable.
Please see an example as follow below. Red means wrong value measure is returning. I suspect the problem is at the final part, but I confess I cannot see a solution rightnow.
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 ) ) ) ) )
Thanks a lot in advance,
Community, could you please try to help?
Using the same SampleFile I shared yesterday, I am sharing now three prints. Wrong view and Expected ones. I think this helps you helping me.
Note that when I select all values in "GRUPO" slicer all works, but when I need to filter a specific one (eg: HMG) then the problem occurs.
Thnaks again in advance,
@Sean, @Greg_Deckler, @Mi2n and Community. THANK YOU SO MUCH for your patience
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!
What is the error that you are getting?
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |