Summing based on another modules field

Options
MatthewWilcox
MatthewWilcox Member, ALL USERS Posts: 5 New Contributor
in Planning & Modeling

Hi team,

 

Hopefully a straight forward one.

 

I have 2 lists. Campaign and Show ID

 

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.

 

Any help would be appreciated.

Tagged:

Comments

  • MatthewWilcox
    MatthewWilcox Member, ALL USERS Posts: 5 New Contributor

    Hi @JaredDolich ,

     

    Is my INP01 campaign details not enough to show the relationship between campaign and showID?

     

    Could I add the showID as a property of the campaign list and would that be sufficient?

     

    Thank you.

  • piyushbund77
    piyushbund77 Member, ALL USERS Posts: 1 Not applicable

    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?

    Something seems wrong here.

    2
    Reply
    Share
    ReportSaveFollow
     
     
     
    level 2
     
    OP

Categories