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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
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
dramus
Continued Contributor
Continued Contributor

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
Anonymous
Not applicable

I am having difficulty replicating the formula.

 

I have a GBP value column [Settlement] and a text value colum [Channel].

 

I need to see what percentage of the total [Settlement] is for each [Channel]

 

Thanks

 

Dom

Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
dramus
Continued Contributor
Continued Contributor

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.

Anonymous
Not applicable

Greetings 

Can you kindly share the file please

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
Continued Contributor
Continued Contributor

Just so I'm clear, the percent of total is a column as opposed to a measure, correct?

 

Yes, both of these were columns.

dramus
Continued Contributor
Continued Contributor

What does your formula look like?

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])

dramus
Continued Contributor
Continued Contributor

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)

That did it! Thank you!!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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