Measure Calculation

Question

Measure Calculation

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

texasphill SSC-Addicted Points: 476 More actions · Answer 1

Adding the clustered index helped, at least now I can see what is happening. The fact table is also joined to a DimProduct, DimClient and DimDate. When I filter to 1 client, 1 year, and 1 product, instead of returning 3 rows it returns 8000...which includes other years and other products. So I looked at the dimension usage tab and see that the fact measure group has relations of type regular defined for all 4 dimension tables. The Dim Contract measure group has a regular relation to DimContract and a many to many with the fact table. I deleted the many to many thinking that was the issue but it just runs longer and had to cancel after an hour. This is what it looks like.

texasphill SSC-Addicted Points: 476 More actions · Answer 2

I have the calculated measure half working. At the detail level the numbers are correct but the subtotal and grand totals are wrong. This is what I get:

Calendar Year	Test	Gross Contracts
2014	$1,000	3
2015	$1,000	588
2016	$1,000	926
2017	$1,000	2,615
2018	$1,000	4,715
2019	$156	2
Grand Total	$1,000	8,849

Each contract has either $78 (no claims) or $1000 (if it has claims). The 2 contracts in 2019 have no claims and the subtotal is correct. The other years have a mixture of contracts with/without claims but the total is always $1000. So it's not even summing those at all. All help is very much appreciated. Thanks.

RonKyle SSC-Dedicated Points: 31573 More actions · Answer 3

As it seems as if you're waiting for an answer, I'm not going to be able to look at this until the weekend. I want to review one of my solutions before commenting further.