Forum Discussion

tlenzmeier's avatar
tlenzmeier
Icon for Helper II rankHelper II
8 years ago
Solved

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 ex...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago
    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)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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:

 

 

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

tlenzmeier's avatar
tlenzmeier
Icon for Helper II rankHelper II
8 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    What does your formula look like?

    • tlenzmeier's avatar
      tlenzmeier
      Icon for Helper II rankHelper II
      8 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago
        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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

     

    Yes, both of these were columns.