cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tlenzmeier Helper II
Helper II

Percent of Column Total

Hello,

 

In what would apparently seem like a staightforward thing to do, I am trying to capture the percent of each contract with a customer to the total contract amount with said customer. For example, customer A has three contracts, each for $100,000. The total for that customer is $300,000 and the percentage of each contract is 33.33%. I have tried the following DIVIDE(sum('Contract'[Net Contract Amount]), CALCULATE([Net Contract Amount], ALLSELECTED('Customer'[CustomerID]))). The value that comes back for each row is 100%. There is somethign amiss with my context since the only way you can get to 100% for each row is that the measure is dividing each row by itselt: $100,000 divided by $100,000 equals 1.  The measure is to be used primarily in a table. Thanks for your assistance.

1 ACCEPTED SOLUTION

Accepted Solutions
dramus Responsive Resident
Responsive Resident

Re: Percent of Column Total

Percent of Total = DIVIDE(sum('Contract'[ContractAmount], RELATED('Vendor'[Vendor Total]), 0)

 

Take out that sum. You don't need it.

 

Percentage of Total Contracts = divide([Contract Amount],related('Companies'[Total Contract Value]),0)

View solution in original post

14 REPLIES 14
Super User IV
Super User IV

Re: Percent of Column Total

Just use the "Show value as" | "Percent of grand total"?

 

Click the little arrow drop down on your value column in the Visualizations area and choose those menu options.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

tlenzmeier Helper II
Helper II

Re: Percent of Column Total

I need to store a value since I have to then multiply that percentage against another value. In this example, the customer has a credit limit of $750,000, so each row in the table needs to have the percentage multiplied by the credit limit.

Super User IV
Super User IV

Re: Percent of Column Total

OK, what you probably want is an ALLEXCEPT clause instead of ALLSELECTED. Sample data please.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

tlenzmeier Helper II
Helper II

Re: Percent of Column Total

Contract NumberVendorIDContract AmountPct of Total
7223DIAM49113$959,57441.99%
7239DIAM49113$805,79035.26%
7351DIAM49113$519,67022.74%
  $2,285,034100.00%

 

The percent of total is a simple formula in Excel. The percentages will subsequently be used in another measure/calculation. If I use ALLEXCEPT, all except what? The VendorID is the primary key in the vendor table and subcontract number is a dependency.

dramus Responsive Resident
Responsive Resident

Re: Percent of Column Total

I created a table with just the companies and then added column based on this:

 

 

Total Contract value = sumx(Calculatetable('Contracts',filter('Contracts',[Company]='Companies'[Company])),[Contract Amount])

 

Then on the Contracts table I created another column based on this:

 

 

Percentage of Total Contracts = [Contract Amount]/related('Companies'[Total Contract Value])

 

 

 

Which gave the following results:

 

Capture.PNG

 

I can let you have the PBIX file if you would like.

Super User IV
Super User IV

Re: Percent of Column Total

ALLEXCEPT whatever filters you are trying to preserve. So, in the case below you would probably just need to use VendorID I believe.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

tlenzmeier Helper II
Helper II

Re: Percent of Column Total

When I go to calculate the percentage, I get a DAX error: "The column either doesn't exist or doesn't have a relationship to any table available in the current context." I do have a relationship defined between the contract table and the vendor table. The contract table is the "many" side of the relationship. I do know that the total amount at the vendor level is correct. Just so I'm clear, the percent of total is a column as opposed to a measure, correct?

dramus Responsive Resident
Responsive Resident

Re: Percent of Column Total

What does your formula look like?

tlenzmeier Helper II
Helper II

Re: Percent of Column Total

Percent of Total = DIVIDE(sum('Contract'[ContractAmount], RELATED('Vendor'[Vendor Total]), 0)

I also have a measure defined in the contracts table Contract Amount = sum('Contract'[ContractAmount])

On the vendor side, I have SUMX(CALCULATETABLE('Contract', FILTER('Contract', [VendorID] =  'Vendor'[VendorID])), [Contract Amount])

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors