Multiple campaigns can have the same show however they are not within the same hierarchy.
I have a module INP01:Campaign Details that has the Show ID as a line item - not all campaigns have show IDs.
I have another module INP02:Episodes where the user inputs the number of episodes that a show will play in a given month. A show should have the same number of episodes in a month (called Unique Episodes) regardless of the campaign and users sometimes only update one campaign instead of all of them.
I am trying to create a formula to detect when the episodes in the month for any given campaign is not equal to the average for the show - this will detect if a campaign has not been updated.
in my head the formula is something along the lines of IF ISNOTBLANK('INP01: Campaign Details'.Show ID) THEN if Unique Episodes <> Unique Episodes[Sum:'INP01:Campaign Details'.'Show ID']/Unique Episodes[Count:'INP01:Campaign Details'.'Show ID'] then false else true else true
say we had 3 campaigns to a show id and 4 episodes in a month the above formula would be 4 = 12/3 which is correct. If one was changed to 5 you would have (4 or 5 depending on campaign) <> 13/3 which would flag as needing correction.
The sum function does not seem to work. I thought lookup would also work but that is not the case.
Great use case. In order to use the SUM function there has to be a relationship between the campaign and the show ID somewhere. The most optimal way would be to create a list that has all the combinations of campaigns and shows to reduce sparsity. Another way is to create a module that has both campaigns and events. Once the relationship is established the SUM function can be used and you can sum either on campaign or show depending on what the target module list is using.
So... wait, I'm not sure I totally understand you.
You want a measure that sums LBS and KGS... together?
What if I select one group that requires LBS and another that requires KGS? I'm then looking at a completely invalid number (some combination of lbs and kgs). Like, if Group 1 requires lbs and Group 2 requires kgs, if I select both, I'm looking at a measure that says "720", which is composed of 500 lbs and 220 kgs?