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...
Measures
  • Cmcmahan's avatar
    Cmcmahan
    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]
)

     

     

Cmcmahan's avatar
Cmcmahan
Resident Rockstar
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]
)