Forum Discussion

mrlang02's avatar
mrlang02
Frequent Visitor
7 years ago
Solved

Measure: Exclude groups based on subgroup values

Hi!     I am trying to create a measure based on summarizing variables (such as the average "Grams of Material") by Production Group (see data example below).     Production Groups contain Produc...
  • Cmcmahan's avatar
    7 years ago

    You should be able to use the IN keyword or the CONTAINS function for this.  I don't think you can use GROUPBY syntax for this, because while it is efficient, it comes with major limitations, like not allowing you to use non-AggregateX-style functions.  

     

    I'm sure there's a more efficient way of doing this, but I was able to get the correct results with this:

     

    Exclude Incomplete = 
    AVERAGEX(
        FILTER(
            ADDCOLUMNS( 
                SUMMARIZE('Table',[Production Group]),
                "NoIncompletes", CALCULATE(NOT CONTAINS('Table', 'Table'[Processsing complete?], "Incomplete")),
                "Production Group Yield",CALCULATE(SUM([Grams of Material]))
            ), 
            [NoIncompletes]
        ),
        [Production Group Yield]
    )