Forum Discussion
Measure: Exclude groups based on subgroup values
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] )
I have a further question about the code from Cmmahan. It works great, but I don't entirely understand how.
My understanding so far would be that the new input table to AVERAGEX is being created by applying the FILTER and ADDCOLUMNS functions on my original table. This table has 2 columns "NoIncompletes" and "Production Group Yield". I would think that the value of the "NoIncompletes" table would be a TRUE FALSE statement. If that is true, where is it specified to only evaluate "TRUE" values in the final average?
Cmmahan's Code:
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] )
And I have another follow-up question. I also have a Production Date Column. A single Production Group always has a single production date.
| Production Group | Production Date | Product ID | Processsing complete? | Grams of Material |
| A | 1/1/19 | A-2 | Complete | 22000 |
| A | 1/1/19 | H-3 | Complete | 35000 |
| A | 1/1/19 | T-27 | Complete | 90000 |
| A | 1/1/19 | X-23 | Complete | 100000 |
| B | 1/15/19 | T-29 | Complete | 78600 |
| B | 1/15/19 | X-53 | Complete | 50000 |
| B | 1/15/19 | A-1 | Incomplete | |
| B | 1/15/19 | C-9 | Incomplete |
The following code produces a 3 month rolling average of yields summarized by production date (which also corresponds to grouping by production group). However, this method used the GROUPBY statement, and I am not sure how to adapt it to only include production groups with COMPLETE data, as accomplished by the code supplied by cmmahan (above). Any advice on how to adapt this is appreciated!!
3 Month RA Wet Yield Per Harvest (g):=CALCULATE (
averagex(
GROUPBY(
Table,
[Production Group Date],
"Production Group Yield",
SUMX(
CURRENTGROUP(),
[Grams of Material])),
[Production Group Yield]),
DATESINPERIOD (
[Production Group Date],
LASTDATE [Production Group Date] ),
-3,
MONTH
)
)
I've highlighted in blue where it checks whether NoIncompletes is true:
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]
)
It's shorthand, since FILTER is evaluating whatever expression for you give as the second parameter for True vs False. Since [NoIncompletes] is either true or false, I can skip the test vs TRUE(). You could easily replace the blue section with [NoIncompletes] = TRUE() and the expression would work exactly the same. If that makes it easier to read for you or for future maintainers of this code, go for it.
As far as updating your rolling average to work with my measure, I'd suggest doing it the other way around. Update my measure to use the rolling timeframe. GROUPBY does a very similar thing to using SUMMARIZE on only pre-existing columns. It's much more efficient than the ADDCOLUMNS/SUMMARIZE syntax, but has a lot of restrictions as a result. Since it only works with the AGGREGATIONX expressions, I'm not sure how to use groupby here.
I haven't tested it in PBI, but adding this as an extra filter condition should work.
Exclude Incomplete Rolling =
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],
DATESINPERIOD (
[Production Group Date],
LASTDATE [Production Group Date] ),
-3,
MONTH
)
),
[Production Group Yield]
)