Having boolean true for certain hierarchy levels only

Options
tasianna05
tasianna05 Member, ALL USERS, Community Member Posts: 5 Occasional Contributor
in Planning & Modeling

I have a hierarchy consisting three levels (product/product family/total products. Similar to the one in the trainings) and I tried to do some conditional formatting on it (need to use it as other tables on dashboard have list/line item subsets in it so needed to create a consistent outlook)

 

I thought using an if formula like this: IF Product only? THEN 0 ELSE IF Product family only? THEN 1 ELSE 2 would work but cannot put together a correct module which contains booleans for both hierarchy levels.

I need something like this one, but I only can figure out for two hierarchy levels. (this formula runs to error: Level mismatch on common dimension)

 

tasianna05_0-1627980239911.png

 

Tagged:

Comments

  • AjayM
    AjayM Member, ALL USERS, GroupMember, Partner, Employee, Community Member, Certified Model Builder Posts: 5 Occasional Contributor

    Hi @tasianna05 ,
    Hope this answers your concern.

    1. Define count lineitems for P1 and P2 with different summaries (Sum and Formula respectively)

    2. Add another lineitem 'P1 vs P2' with a summary ratio to gather different output for each level

    Module BP1.png

    3. You can use 'P1 vs P2' lineitem for CF or define another one like below
    Module DV.png

  • tasianna05
    tasianna05 Member, ALL USERS, Community Member Posts: 5 Occasional Contributor

    Hi @AjayM 

     

    Yes it worked! Slight modification I've done as I do not have equal amount of products under the product family:

    IF 'P1 vs P2' = 1 THEN 2 ELSE IF 'P1 vs P2' <> 'P1 vs P2'[SELECT: Top Level parent] THEN 1 ELSE 0

     

    Thank you @AjayM and @KirillKuznetsov  the help!

     

    Anna

Categories