Forum Discussion
Create a measure with value from another table
That worked. Thank You!
Hi ddeutschman,
You can share the table structure and the measure formula, I will build some sample data to test.
Regards,
XIaoxin Sheng
Here are the schemas for the two tables in question:
| ud_Service_Sales | |||||||||||||||||||||||
| COLUMN_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_CATALOG | CHARACTER_SET_SCHEMA | CHARACTER_SET_NAME | COLLATION_CATALOG | COLLATION_SCHEMA | COLLATION_NAME | DOMAIN_CATALOG | DOMAIN_SCHEMA | DOMAIN_NAME |
| ARCo | Viewpoint | dbo | udService_Sales | ARCo | 1 | NULL | NO | tinyint | NULL | NULL | 3 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Viewpoint | dbo | bCompany |
| Contract | Viewpoint | dbo | udService_Sales | Contract | 2 | NULL | YES | varchar | 10 | 10 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | Latin1_General_BIN | Viewpoint | dbo | bContract |
| Mth | Viewpoint | dbo | udService_Sales | Mth | 3 | NULL | NO | smalldatetime | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | Viewpoint | dbo | bMonth |
| TranDate | Viewpoint | dbo | udService_Sales | TranDate | 4 | NULL | NO | smalldatetime | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | NULL | Viewpoint | dbo | bDate |
| Invoiced | Viewpoint | dbo | udService_Sales | Invoiced | 5 | NULL | NO | numeric | NULL | NULL | 12 | 10 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Viewpoint | dbo | bDollar |
| udSALESMAN | Viewpoint | dbo | udService_Sales | udSALESMAN | 6 | NULL | YES | int | NULL | NULL | 10 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Viewpoint | dbo | bEmployee |
| Department | Viewpoint | dbo | udService_Sales | Department | 7 | NULL | NO | varchar | 10 | 10 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | Latin1_General_BIN | Viewpoint | dbo | bDept |
| vbudServiceSalesPlan | |||||||||||||||||||||||
| COLUMN_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_CATALOG | CHARACTER_SET_SCHEMA | CHARACTER_SET_NAME | COLLATION_CATALOG | COLLATION_SCHEMA | COLLATION_NAME | DOMAIN_CATALOG | DOMAIN_SCHEMA | DOMAIN_NAME |
| Co | Viewpoint | dbo | vbudServiceSalesPlan | Co | 1 | NULL | NO | tinyint | NULL | NULL | 3 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Viewpoint | dbo | bCompany |
| Company | Viewpoint | dbo | vbudServiceSalesPlan | Company | 2 | NULL | YES | tinyint | NULL | NULL | 3 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Viewpoint | dbo | bCompany |
| Employee | Viewpoint | dbo | vbudServiceSalesPlan | Employee | 3 | NULL | YES | int | NULL | NULL | 10 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Viewpoint | dbo | bEmployee |
| Plan_Annual_Margin | Viewpoint | dbo | vbudServiceSalesPlan | Plan_Annual_Margin | 4 | NULL | YES | numeric | NULL | NULL | 9 | 10 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Plan_Annual_Sales | Viewpoint | dbo | vbudServiceSalesPlan | Plan_Annual_Sales | 5 | NULL | YES | numeric | NULL | NULL | 9 | 10 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Plan_Monthly_Margin | Viewpoint | dbo | vbudServiceSalesPlan | Plan_Monthly_Margin | 6 | NULL | YES | numeric | NULL | NULL | 9 | 10 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Plan_Monthly_Sales | Viewpoint | dbo | vbudServiceSalesPlan | Plan_Monthly_Sales | 7 | NULL | YES | numeric | NULL | NULL | 9 | 10 | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Rec_Key | Viewpoint | dbo | vbudServiceSalesPlan | Rec_Key | 8 | NULL | NO | int | NULL | NULL | 10 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Start_Month | Viewpoint | dbo | vbudServiceSalesPlan | Start_Month | 9 | NULL | YES | tinyint | NULL | NULL | 3 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| Year | Viewpoint | dbo | vbudServiceSalesPlan | Year | 10 | NULL | YES | smallint | NULL | NULL | 5 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| UniqueAttchID | Viewpoint | dbo | vbudServiceSalesPlan | UniqueAttchID | 11 | NULL | YES | uniqueidentifier | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| KeyID | Viewpoint | dbo | vbudServiceSalesPlan | KeyID | 12 | NULL | NO | bigint | NULL | NULL | 19 | 10 | 0 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| FullName | Viewpoint | dbo | vbudServiceSalesPlan | FullName | 13 | NULL | YES | varchar | 83 | 83 | NULL | NULL | NULL | NULL | NULL | NULL | iso_1 | NULL | NULL | Latin1_General_BIN | NULL | NULL | NULL |
I created a custom column Salesman_Key in udService_Sales which consisted concatenating of a copy of the column ARCo (changed to text) and udSALESMAN (Changed to text).
I created a custom column Salesman_Key in vbudServiceSalesPlan which consisted concatenating of a copy of the column Co (changed to text) and Employee (Changed to text).
I then created a relationship between the two tables with a one to many (vbudServiceSalesPlan --> udServiceSales.
I created a custom column TotalYTDSales = CALCULATE(sum(udService_Sales[Invoiced]), YEAR(udService_Sales[Mth])=2017)
I then attempted to create the Measure YTD % of Plan = udService_Sales[TotalYTDSales]/vbudServiceSalesPlan[Plan_YTD_Sales]
This is when the error was generated "A single value for column 'Plan_YTD_Sales' in table 'vbudServiceSalesPlan' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.".
It is a column Plan_YTD_Sales = [Months]*[Plan_Monthly_Sales]
You only need to add a aggregation in the % Formula.
YTD % of Plan = udService_Sales[TotalYTDSales]/sum(vbudServiceSalesPlan[Plan_YTD_Sales])
Assuming that ServiceSalesPlan have only 1 year. If is not you i recommend create a measure to filter the year.
