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

     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago
-- 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