Home Forums Power Pivot create a column that compares child level aggregate to a value on a target table

This topic contains 2 replies, has 2 voices, and was last updated by  dfish 3 weeks, 5 days ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #18128

    dfish
    Participant
    • Started: 6
    • Replies: 16
    • Total: 22

    I have data like the following

    Style id     Color    Qty     Category Sub-Category

    A                 Blue       2       Bike         Men’s

    A                 Red        3       Bike         Women’s

    Pivot

    Style id   Color     Units

    A                               5

    Blue         2

    Red          3

    I am trying to add two calcs / columns – one that compares the style units to a min expected style value at the category / sub-category level and another that compares the style color level units to a min expected style color value at the category , sub category level.

    I tried using AllExcept to get the total units down at the style/color level, but in this column it still shows the total units at the style level of the pivot. What I am trying to do is create a column that compares the units at the style / color level to a an expected target min value at the category/ sub-category level in another table so how do I only do that comparison at the style/color level of the pivot and not the parent style level. I then need to create another column that does this same comparison to a target at the parent level only to a parent level expected minimum value. Any links here to any suggested DAX techniques that might work. Worst case scenario I will just add the targets to the rows of the pivot where a column can be placed outside of the pivot with an if statement that checks if the style id has a value and the color is blank and then if the units is < target then 1 else 0. I was just wondering if there was a better way to do it.

    #18129

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2580
    • Total: 2587

    Try looking into the function HASONEFILTER to check the drill down level. If the target table has no relationship with the other tables, LOOKUPVALUE should be able to capture the target amount.

    Hard to say exactly without having a full example to work with.

    #18130

    dfish
    Participant
    • Started: 6
    • Replies: 16
    • Total: 22

    Thank you Tom.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.