ssas - 每个公共维度成员的计算度量
问题描述
给定下表:
CREATE TABLE [Dimensie].[Cao]
(
[Cao key] INT NOT NULL IDENTITY(1,1),
[Cao business key] NVARCHAR(255) NOT NULL,
CONSTRAINT [PK Cao] PRIMARY KEY ([Cao key]),
CONSTRAINT [UC Cao business key] UNIQUE ([Cao business key])
);
CREATE TABLE [Dimensie].[Datum]
(
[Datum key] INT NOT NULL,
[Datum business key] DATETIME NOT NULL,
[Jaar] INT NOT NULL,
[Maand] INT NOT NULL,
[Dag van de maand] INT NOT NULL,
[Datum] NVARCHAR(255) NOT NULL
CONSTRAINT [PK Datum] PRIMARY KEY ([Datum key]),
CONSTRAINT [UC Datum business key] UNIQUE ([Datum business key])
);
CREATE TABLE [Personeel].[Contractuele uren]
(
[Datum key] INT NOT NULL,
[Cao key] INT NOT NULL,
[Contractuele uren] NUMERIC(15,6) NOT NULL,
CONSTRAINT [FK Contractuele uren Datum] FOREIGN KEY ([Datum key]) REFERENCES [Dimensie].[Datum] ([Datum key]),
CONSTRAINT [FK Contractuele uren Cao] FOREIGN KEY ([Cao key]) REFERENCES [Dimensie].[Cao] ([Cao key]),
CONSTRAINT [UC Contractuele uren] UNIQUE ([Datum key], [Cao key])
);
CREATE TABLE [Personeel].[Werklast uren]
(
[Datum key] INT NOT NULL,
[Cao key] INT NOT NULL,
[Werklast uren] NUMERIC(15,6) NOT NULL,
CONSTRAINT [FK Werklast uren Datum] FOREIGN KEY ([Datum key]) REFERENCES [Dimensie].[Datum] ([Datum key]),
CONSTRAINT [FK Werklast uren Cao] FOREIGN KEY ([Cao key]) REFERENCES [Dimensie].[Cao] ([Cao key])
);
所以Personeel
架构中的两个表都引用了架构中的两个表Dimensie
。
我SSAS tabular
根据这些表生成了一个项目,并采取了以下措施:
Contracturen := SUM('Contractuele uren'[Contractuele uren])
Uren/FTE := SUM('Werklast uren'[Werklast uren])
Contract FTE := 'Contractuele uren'[Contracturen]/'Werklast uren'[Uren/FTE]
这给了我以下结果([Dimensie].[Datum].[Maand]
在行上,[Dimensie].[Cao].[Cao business key]
在列上):
每个月都可以。但是,总计是两个总和的除法,而我希望它是成员36
和小计的总和45
。
如何更改度量的定义Contract FTE
以实现此目的?
解决方案
在总结之前,使用迭代器对各个成员执行除法:
Contract FTE :=
SUMX(
VALUES('Cao'[Cao business key]);
DIVIDE('Contractuele uren'[Contracturen]; 'Werklast uren'[Uren/FTE])
)