Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
130 | |
110 | |
93 | |
70 | |
67 |