Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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% |
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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% |
Hi,
You may download my PBI file from here.
Hope this helps.
This is the second time that I find a solution proposed by you, but you don't put the resolution here. The .pbi file is no longer available for download. It would be much better to put it step by step here, wouldn't it?
I do not have the file. Post your data, explain the question and show the expected result.
Appreciate, thank you so much.
You are welcome.
Just wanted to add, in above table for first row percentage calculation would be like (2/16) rounded to near number following to next row like (3/16) rounded to near number.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
97 | |
88 | |
70 |
User | Count |
---|---|
165 | |
131 | |
129 | |
102 | |
98 |