Forum Discussion
Percentages on Subtotal
Hi,
I am triying to calculate percentages based on Subtotal percentage(as 100%), can please help how to manage with DAX to achieve this. For example I copied from excel and wanted to achieve same in Power Bi. For example first row % column calculted like Sum(counts with respect to Load)/Count = 3/(3+6+3+1+2+2) = 18% and similerly 6/(3+6+3+1+2+2)=35%. We are capturing counts at report level. Please advise.
| Dimension | Dimension2 | Counts | % |
| Load | Health | 3 | 18% |
| Load | Roles | 6 | 35% |
| Load | MGMT | 3 | 18% |
| Load | NIT | 1 | 6% |
| Load | SBMT | 2 | 12% |
| Load | Balance | 2 | 12% |
| Proofing | Health | 3 | 5% |
| Proofing | Roles | 21 | 32% |
| Proofing | MGMT | 12 | 18% |
| Proofing | NIT | 5 | 8% |
| Proofing | SBMT | 23 | 35% |
| Proofing | Balance | 1 | 2% |
8 Replies
You can use a measure expression like this one. Replace Data with your actual table name.
NewMeasure =
VAR vCount =
SUM ( Data[Counts] )
VAR vCountAllDim2 =
CALCULATE ( SUM ( Data[Counts] ), ALL ( Data[Dimension2] ) )
RETURN
DIVIDE ( vCount, vCountAllDim2 )Pat
Hi Pat,
Thank you, I think I am missing something here, we ar actually getting counts at report level (by selecting on values) but we do not have separate measure for counts. Is it something I should create? please advise. below the core data for example.
ID Segment Level 70012 Balance Load 70015 Roles Load 70035 Balance Load 70045 SBMT Load 70047 Roles Load 70049 Health Load 70062 MGMT Load 70065 Health Load 70071 MGMT Load 70103 Roles Load 70111 SBMT Load 70117 MGMT Load 70118 Health Load 70127 Roles Load 70141 Roles Load 70150 Roles Load 70010 Roles Proofing 70016 SBMT Proofing 70022 Roles Proofing 70023 SBMT Proofing 70028 SBMT Proofing 70048 SBMT Proofing 70069 SBMT Proofing 70080 Roles Proofing 70089 MGMT Proofing 70096 Roles Proofing 70105 Roles Proofing 70112 Roles Proofing and I am trying to achieve percentages like below
Level Segment Count Percentage Load Balance 2 13% Load Health 3 19% Load MGMT 3 19% Load Roles 6 38% Load SBMT 2 13% Proofing MGMT 1 8% Proofing Roles 6 50% Proofing SBMT 5 42%
