Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrlang02
Frequent Visitor

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 Products, identified by the Product ID field.  When that Product is finished being produced, it is marked as "Complete" and the "Grams of Material" are entered.

 

I am able to use the GROUP by and SUMX, AVERAGEX, CALCULATE and FILTER functions to summarize by groups and exclude certain rows.  However, I do NOT want to summarize any Production Groups that include "Incomplete" Products.  

 

For instance, in the sample data below, I would not want to see any reporting on Production Group B, as some Products within that group are "Incomplete".   

 

Please let me know how you might accomplish this.  

 

Here is some sample code that 1) sums grams of product within each production group, and 2) takes the average of these groups. It does not exclude the groups with incomplete products, which is the step I'm missing.

```
averagex(
GROUPBY(Table,[Production Group],"Production Group Yield",
SUMX(CURRENTGROUP(),[Grams Material])),[Production Group Yield])

```

 

Production GroupProduct IDProcesssing complete?Grams of Material
AA-2Complete22000
AH-3Complete35000
AT-27Complete90000
AX-23Complete100000
BT-29Complete78600
BX-53Complete50000
BA-1Incomplete 
BC-9Incomplete 
1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar

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]
)

 

 

View solution in original post

6 REPLIES 6
mrlang02
Frequent Visitor

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 GroupProduction DateProduct IDProcesssing complete?Grams of Material
A

1/1/19

A-2Complete22000
A1/1/19H-3Complete35000
A1/1/19T-27Complete90000
A1/1/19X-23Complete100000
B

1/15/19

T-29Complete78600
B1/15/19X-53Complete50000
B1/15/19A-1Incomplete 
B1/15/19C-9Incomplete 

 

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]
)
mrlang02
Frequent Visitor

Thank you so much!  Works perfectly!

mrlang02
Frequent Visitor

Thank you all for your help!  I marked the solution I used.  Did not end up trying others, but I will note them for next time.  

Anonymous
Not applicable

-- A measure that you could place next to the name
-- of a production group in a visual.
[Average For Group] = 
-- This var informs if only one Production Group is
-- visible in the current context and if there is a
-- direct filter on the column.
var __oneGroupVisible = HASONEFILTER( T[Production Group] )
-- This tells you if the group, as a whole, has
-- at least one product in the Incomplete state.
-- It disregards any filtering on the expanded table
-- and only keeps the filter on the whole group.
var __groupHasIncompleteProducts =
	NOT ISEMPTY(
		CALCULATETABLE(
			VALUES( T[Processing complete?] ),
			ALLEXCEPT( T, T[Production Group] ),
			T[Processing complete?] = "Incomplete"
		)
	)
-- This average is calculated only over the visible
-- products in the group. If some products have been
-- filtered out, they will not be considered by the
-- average. But bear in mind that the var above does
-- not respect any other filters than the one place
-- on the Production Group itself. This is probably
-- what you wanted.
var __avg = AVERAGE( T[Grams of Material] )
var __shouldCalculate = TRUE()
	&& __oneGroupVisible
	&& NOT ( __groupHasIncompleteProducts )
return
	if(
		__shouldCalculate,
		__avg
	)

Best

Darek

Cmcmahan
Resident Rockstar
Resident Rockstar

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]
)

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors