This is a much simplified example of an issue I've got working with multiple data sources that have been related together (at logical level, not joined at physical level).
At the detailed level I have
Month Item Group Cost Sale
01/01/2021 1 A 100 110
01/01/2021 2 B 50 60
01/01/2021 3 B 70 80
01/02/2021 1 A 110 120
01/02/2021 2 B 60 70
01/02/2021 3 C 75 85
And at an aggregate level I have
Month Group Bonus
01/01/2021 A 20
01/01/2021 B 50
01/02/2021 A 20
01/02/2021 B 0
Which have been related together on Month and Group. Note that in the aggregate data there is no Group C.
What I need to do is assign a proportion of Bonus across all the sales for the group in the month and then use that figure to calculate profit as [Sale] - [Cost] + [Bonus Proportion]. But for group C I always end up with a missing value.
Normally this is fixed by wrapping the null in ZN(), but that is not working in this instance and I don't understand why not.
Attached workbook illustrates the issue.
Hi Jonathan,
It's because the record doesn't exist in the fixed calculation in the bonus spread calculation - and it's not a null, so there's nothing to convert a 0 to.
In this case, try a join instead rather than a relationship. I get these results:
Updating your bonus spread calc to:
( {FIXED [Group (AggregateData)],[Month (AggregateData)] : MIN([Bonus])}
/
{FIXED [Group],[Month]: SUM([Sale])}
)
*
[Sale]
Please see attached workbook. If this post answers your question, please mark as best answer to help others find the solution.
Thanks,
Mavis
