Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filtering on a related table condition

Hi,

 

I have a build to forumla where I'm calculating the average production needs for a group of item.  However, we have a new requirement where I need to modify the calculation based on the item type.   For example, if it's Type B, multiple the generated value by 1.5 .

 

Build to = 
VAR week = MAX(Dates[Week of Year])
VAR year = MAX(Dates[Year])
Var weeklyAvg = 
-CALCULATE( SUMX( 'Item Ledger', DIVIDE('Item Ledger'[Quantity], 'Item Ledger'[Case Equiv], 0) ),
    'Item Ledger'[Entry Type]=4,   
    'Item Ledger'[Location Code] = "1",
     FILTER(ALL(Dates) , Dates[Week of Year] < week  &&  Dates[Week of Year] > week -5 && Dates[Year] = year     
    ) 

)/4

RETURN weeklyAvg

 

However, I can't seem to figure out how to do that.  I would have thought something like

 

If('item'[product group code] = "xxx" ,  weeklyAvg  , weeklyAvg *1.5)

 

Would work, but when I try and get the item table, it doesn't show.  So I figure what I probably need is relatedtable function  But I'm not sure how to use that in this scenario.

 

Thanks,

 

 

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    Could you please provide some sample data in Dates , Item, and Item Ledger in order to make troubleshoting and provide you the correct measure? Is there any relationship created among these three tables? If yes, please provide the base fields and cardinality. And please also provide your desired results. It is better if you can provide your sample pbix file(You can upload it to OneDrive for Business and share the link with us). Thank you. 

    Best Regards

    Rena

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Ok,

       

      Here is a link to the sample file, very similiar structure to my real data,

       

      https://drive.google.com/file/d/1thGZOJJuhPuUJkCuKvfVMVyCXI8KQOXi/view?usp=sharing

       

      Here is a modified version of the measure I'm working on

       

      Build to = 
VAR week = MAX(Dates[Week of Year])
VAR year = MAX(Dates[Year])

VAR weeklyAvg = CALCULATE(SUMX('Item Ledger','Item Ledger'[Quantity]),
                    FILTER(ALL(Dates), Dates[Week of Year] < week && Dates[Week of Year] > week -5 && Dates[Year] = year ) 
                 ) /4


RETURN weeklyAvg   //  Want to vary by 'item'[category]

       

      And the sample report

       

       

      Basically I want the forumula to say if 'item'[category] = "Cultured"  then multiply the "Build To" value by some factor (say 1.5).  So Sour Cream instead of being 365 would be 547.5

       

      Yes the tables are related in a standard one way filter

       

       

       

      Thanks,