Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi...I am trying to get the percentage of sub totals. See the below Excel file. For example for Jane Smith, we have percentages for CAP, RD, RER, and S199 which shows the percentage that CAP is compared to the subtotal for Jane Smith of $373,680. The formula I have in Excel is for CAP is $17,409 / $373,680 = 4.66% or for RD is $220,828 / $373,680 = 59.10%. Any ideas on how I do this in Power BI?
Thanks
Eng Partner | Svc Code | FY18 YTD TER | |
Smith, Jane | CAP | $17,409 | 4.66% |
Smith, Jane | RD | $220,828 | 59.10% |
Smith, Jane | RER | $47,433 | 12.69% |
Smith, Jane | S199 | $88,010 | 23.55% |
Total | $373,680 | ||
Jones, Joe | CAP | $207,531 | 67.52% |
Jones, Joe | INV | $14,734 | 4.79% |
Jones, Joe | ME | $11,468 | 3.73% |
Jones, Joe | RER | $10,860 | 3.53% |
Jones, Joe | S174 | $5,440 | 1.77% |
Jones, Joe | S199 | $56,667 | 18.44% |
Jones, Joe | TCA | $646 | 0.21% |
Total | $307,346 | ||
Grand Total | $681,026 |
Solved! Go to Solution.
You may add a measure as shown below.
Measure = DIVIDE ( SUM ( Table1[FY18 YTD TER] ), CALCULATE ( SUM ( Table1[FY18 YTD TER] ), ALLSELECTED ( Table1[Svc Code] ) ) )
You may add a measure as shown below.
Measure = DIVIDE ( SUM ( Table1[FY18 YTD TER] ), CALCULATE ( SUM ( Table1[FY18 YTD TER] ), ALLSELECTED ( Table1[Svc Code] ) ) )
@v-chuncz-msft
Thank you for your reply. It also helped me.
Would it be possible to keep these values fixed when the user uses the slicer to filter by "Svc Code" ?
Because at the moment, in this case, the percentage becomes 100%.
Example: User filter per "CAP"
Current Result:
Eng Partner | Svc Code | FY18 YTD TER | |
Smith, Jane | CAP | $17,409 | 100% |
Jones, Joe | CAP | $207,531 | 100% |
Desired Result:
Eng Partner | Svc Code | FY18 YTD TER | |
Smith, Jane | CAP | $17,409 | 4.66% |
Jones, Joe | CAP | $207,531 | 67.52% |
Thank you so much in advance!
Kind regards,
Jéssica
Thanks...It worked. Appreciate it.
Dear @hkrash0920,
Incase of keeping the original Value, just clone new measure column and using using "Show value as > Percent of column Total".
Regards,
ManNVSM.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |