Dear all,
I need some advice on how I should create my measures in my universe (BOXI R3). So I am going to explain my problem. I have two fields, one is an ID (Field A), the other is a total (Field B).
If I create a measure (X)
COUNT (DISTINCT FIELD A) [default aggregation SUM]
If I create a measure (Y)
SUM (FIELD B) [default aggregation SUM]
If I create a measure (Z)
FIELD B [default aggregation SUM]
If I create a query and put all measures into the Query I would expect Y and Z to be the same, but NO!!!. BO adds a GROUP By for the measure z (e.g. GROUP BY FIELD B]
This will basically have the impact on the following measures:
Measure X -> Will be potentially too high in the report as it will group by measure Z, then in the report sum the counts
Measure Y -> Will be correct
Measure Z -> Will be too low as it has grouped the totals together then sum
My question is AM I MAD…how do you get around this. It either means you must always use an aggregate in your measure (e.g. X and Y) or never then you cant do distinct counts
have I missed something, am I being stupid
If I do X and Y together the figures are fine. If I do Z on its own the figures are fine. I can’t have mixed rules in my universe, my users would never understand and then get incorrect figures depending on what measures they chose.
Any advice on how I should do this would be great as my current resolution is that all measures must be an aggregate (e.g. SUM, COUNT(DISINTCT)) etc
Many thanks (hopefully not too confusing)
Matt
injenuity (BOB member since 2010-04-23)
(BOB member since 2005-01-06)