Forum Discussion
Create a measure with value from another table
That worked. Thank You!
There is confidential information in the tables. Is there some other method of troubleshooting the issue?
Hi ddeutschman,
You can share the table structure and the measure formula, I will build some sample data to test.
Regards,
XIaoxin Sheng
I will provide the views althought I ran into an issue creating the scripts today.
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]
