Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

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. 

 

DimensionDimension2Counts%
LoadHealth318%
LoadRoles635%
LoadMGMT318%
LoadNIT16%
LoadSBMT212%
LoadBalance212%
ProofingHealth35%
ProofingRoles2132%
ProofingMGMT1218%
ProofingNIT58%
ProofingSBMT2335%
ProofingBalance12%
1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
mahoneypat
Microsoft Employee
Microsoft Employee

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 ( vCountvCountAllDim2 )

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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. 

 

IDSegmentLevel
70012BalanceLoad
70015RolesLoad
70035BalanceLoad
70045SBMTLoad
70047RolesLoad
70049HealthLoad
70062MGMTLoad
70065HealthLoad
70071MGMTLoad
70103RolesLoad
70111SBMTLoad
70117MGMTLoad
70118HealthLoad
70127RolesLoad
70141RolesLoad
70150RolesLoad
70010RolesProofing
70016SBMTProofing
70022RolesProofing
70023SBMTProofing
70028SBMTProofing
70048SBMTProofing
70069SBMTProofing
70080RolesProofing
70089MGMTProofing
70096RolesProofing
70105RolesProofing
70112RolesProofing

 

and I am trying to achieve percentages like below

LevelSegmentCountPercentage
LoadBalance213%
LoadHealth319%
LoadMGMT319%
LoadRoles638%
LoadSBMT213%
ProofingMGMT18%
ProofingRoles650%
ProofingSBMT542%

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Appreciate, thank you so much. 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.