Percentages on Subtotal

[Anonymous]

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%

[mahoneypat]

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

[Anonymous]

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%

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[marukosu]

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?

[Ashish_Mathur]

I do not have the file.  Post your data, explain the question and show the expected result.

[Anonymous]

Appreciate, thank you so much. 

[Ashish_Mathur]

You are welcome.

[Anonymous]

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.