Average won't show up at some level in matrix

[AntonioCarl]

Hi,

I want to create a matrix with Product, Oper_id, SerialNO and be able to slice it by slicer which removes n rows. That part is done and it works - sort of. 

Even though it show me correct result on Product and Oper_Id level it doesn't show any data on SerialNo level. Why is that? I know my measure is quite complex.  How do I change something in formula to be able to see duration times at serialno level? 

My matrix is supposed to show SUM on Product level on average duration on Oper_id and SerialNO. SerialNO is filtered by "remove n rows" slicer. 

I've noticed that if I set "remove n rows" slicer to 0 duration shows up. But as soon as I change slicer they dissapear. I belive it is caused by break of data linage, tried many different options non of which worked. 

My formula is bit complex and I'm pretty sure it could be written in more beatiful manner 😉 

AVG Duration without n rows = 
VAR _TempTable =
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[PRODUCT],
            'Table'[OPER_ID],
            'Table'[SERIALNO],
            'Table'[DURATION_S]
        ),
        "RANKING", RANKX(
            FILTER(
                'Table',
                EARLIER('Table'[PRODUCT]) = 'Table'[PRODUCT] &&
                EARLIER('Table'[OPER_ID]) = 'Table'[OPER_ID]
            ),
            'Table'[DURATION_S],
            ,
            ASC,
            DENSE
        )
    )

VAR _MinMaxRanking = 
    ADDCOLUMNS(
        _TempTable,
        "MinRanking", 
        MINX(
            FILTER(
                _TempTable,
                [PRODUCT] = EARLIER([PRODUCT]) &&
                [OPER_ID] = EARLIER([OPER_ID])
            ),
            [RANKING]
        ),
        "MaxRanking", 
        MAXX(
            FILTER(
                _TempTable,
                [PRODUCT] = EARLIER([PRODUCT]) &&
                [OPER_ID] = EARLIER([OPER_ID])
            ),
            [RANKING]
        )
    )

VAR _N = [Remove N Rows Value]

VAR _Filtered = 
    FILTER(
        _MinMaxRanking,
        [RANKING] >= [MinRanking] + _N && [RANKING] <= [MaxRanking] - _N
    )

VAR _AveragePerSerialNumber= 
    SUMMARIZE(
        _Filtered,
        [PRODUCT],
        [OPER_ID],
        [SERIALNO],
        "@AVG", AVERAGEX(
            FILTER(
                _Filtered,
                [PRODUCT] = EARLIER([PRODUCT]) &&
                [OPER_ID] = EARLIER([OPER_ID]) &&
                [SERIALNO] = EARLIER([SERIALNO])
            ),
            [DURATION_S]
        )
    )

-- Sum on Product and Oper_id columns
VAR _Level1and2 = 
    SUMX(
        SUMMARIZE(
            _AveragePerSerialNumber,
            [PRODUCT],
            [OPER_ID],
            "@AVGPerOperId", AVERAGEX(
                FILTER(
                    _AveragePerSerialNumber,
                    [PRODUCT] = EARLIER([PRODUCT]) &&
                    [OPER_ID] = EARLIER([OPER_ID])
                ),
                [@AVG]
            )
        ),
        [@AVGPerOperId]
    )

-- Sum on serial number column
VAR _DurationSerialno = 
    SUMX(
        _AveragePerSerialNumber,
        [@AVG]
    )

VAR _Duration = 
    IF(
        HASONEVALUE( 'Table'[SERIALNO] ), 
        _DurationSerialno,
        _Level1and2
    )

VAR _Hours = INT( _Duration / 3600 )
VAR _Minutes = INT( MOD( _Duration - _Hours * 3600, 3600 ) / 60 )
VAR _Seconds = MOD( MOD( _Duration - _Hours * 3600, 3600 ), 60 )

VAR _Result = 
    IF(
        _Duration <> 0,
        FORMAT(_Hours, "00") & ":" & FORMAT(_Minutes, "00") & ":" & FORMAT(_Seconds, "00")
    )

RETURN 
    _Result

[Dangar332 · Accepted solution]

Hi, @AntonioCarl 

Try Below Measure

Measure 5= 
	VAR Maintable = ALLSELECTED(
		'Table'[PRODUCT],
		'Table'[OPER_ID],
		'Table'[SERIALNO],
		'Table'[DURATION_S]
	)
	VAR RankTable = ADDCOLUMNS(
		Maintable,
		"Rank", RANK(
			DENSE,
			Maintable,
			ORDERBY(
				'Table'[DURATION_S],
				ASC
			),
			,
			PARTITIONBY(
				'Table'[PRODUCT],
				'Table'[OPER_ID]
			)
		)
	)
	VAR Minrank = MINX(
		FILTER(
			RankTable,
			'Table'[PRODUCT] = MIN('Table'[PRODUCT]) && 'Table'[OPER_ID] = MIN('Table'[OPER_ID])
		),
		[Rank]
	)
	VAR maxrank = MAXX(
		FILTER(
			RankTable,
			'Table'[PRODUCT] = MIN('Table'[PRODUCT]) && 'Table'[OPER_ID] = MIN('Table'[OPER_ID])
		),
		[Rank]
	)
	VAR _N = [Remove N Rows Value]
	VAR filtertable = FILTER(
		RankTable,
		[Rank] >= Minrank + _N && [Rank] <= maxrank - _N
	)
	VAR Avgtable_serialno = AVERAGEX(
		FILTER(
			filtertable,
			'Table'[PRODUCT] = MIN('Table'[PRODUCT]) && 'Table'[OPER_ID] = MIN('Table'[OPER_ID])
		),
		[DURATION_S]
	)
	VAR Avgtable_operlevel = AVERAGEX(
		FILTER(
			filtertable,
			'Table'[PRODUCT] = MIN('Table'[PRODUCT]) && 'Table'[OPER_ID] = MIN('Table'[OPER_ID]) && 'Table'[SERIALNO] = MIN('Table'[SERIALNO])
		),
		[DURATION_S]
	)
	VAR _Duration = IF(
		ISINSCOPE('Table'[SERIALNO]),
		Avgtable_operlevel,
		Avgtable_serialno
	)
	VAR _Hours = INT(_Duration / 3600)
	VAR _Minutes = INT(MOD(
		_Duration - _Hours * 3600,
		3600
	) / 60)
	VAR _Seconds = MOD(
		MOD(
			_Duration - _Hours * 3600,
			3600
		),
		60
	)
	VAR result =
	IF(
		_Duration <> 0,
		FORMAT(
			_Hours,
			"00"
		) & ":" & FORMAT(
			_Minutes,
			"00"
		) & ":" & FORMAT(
			_Seconds,
			"00"
		)
	)
	RETURN
		result

You can Download File from below

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AntonioCarl]

Anyone please? @Dangar332 ? 

[Dangar332]

Hi, @AntonioCarl 

Try below measure 

EVALUATE
	VAR a1 = [Remove N Rows Value]
	VAR a = ALLSELECTED(
		'Table'[PRODUCT],
		'Table'[OPER_ID],
		'Table'[SERIALNO],
		'Table'[DURATION_S]
	)
	VAR b = ADDCOLUMNS(
		a,
		"Rank12", RANK(
			DENSE,
			a,
			ORDERBY(
				[DURATION_S],
				ASC
			),
			,
			PARTITIONBY(
				'Table'[PRODUCT],
				'Table'[OPER_ID]
			)
		)
	)
	VAR c = MINX(
		FILTER(
			b,
			'Table'[PRODUCT] = MIN('Table'[PRODUCT]) && 'Table'[OPER_ID] = MIN('Table'[OPER_ID])
		),
		[Rank12]
	)
	VAR d = MAXX(
		FILTER(
			b,
			'Table'[PRODUCT] = MIN('Table'[PRODUCT]) && 'Table'[OPER_ID] = MIN('Table'[OPER_ID])
		),
		[Rank12]
	)
	VAR e = FILTER(
		b,
		[Rank12] >= c + a1 && [Rank12] <= d - a1
	)
	VAR f = SUMX(
		ADDCOLUMNS(
			SUMMARIZE(
				e,
				'Table'[PRODUCT],
				'Table'[OPER_ID],
				'Table'[SERIALNO]
			),
			"average", CALCULATE(
				AVERAGE('Table'[DURATION_S]),
				ALLEXCEPT(
					'Table',
					'Table'[PRODUCT],
					'Table'[OPER_ID],
					'Table'[SERIALNO]
				)
			)
		),
		[average]
	)
	VAR g = SUMX(
		ADDCOLUMNS(
			SUMMARIZE(
				e,
				'Table'[PRODUCT],
				'Table'[OPER_ID],
				'Table'[SERIALNO]
			),
			"average", CALCULATE(
				AVERAGE('Table'[DURATION_S]),
				ALLEXCEPT(
					'Table',
					'Table'[PRODUCT],
					'Table'[OPER_ID]
				)
			)
		),
		[average]
	)
	VAR _Duration = IF(
		ISINSCOPE('Table'[SERIALNO]),
		f,
		g
	)
	VAR _Hours = INT(_Duration / 3600)
	VAR _Minutes = INT(MOD(
		_Duration - _Hours * 3600,
		3600
	) / 60)
	VAR _Seconds = MOD(
		MOD(
			_Duration - _Hours * 3600,
			3600
		),
		60
	)
	VAR _Result =
	IF(
		_Duration <> 0,
		FORMAT(
			_Hours,
			"00"
		) & ":" & FORMAT(
			_Minutes,
			"00"
		) & ":" & FORMAT(
			_Seconds,
			"00"
		)
	)
	RETURN
		_Result

You can download file from below

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AntonioCarl]

@Dangar332Unfortunatelly, it isn't correct.

What I want to achieve is showed on image below. I want to get AVG duration without smallest and biggest serialno within Oper_id - my formula works in that case. 
But I also want to get individual duration of each serialno wchich isn't removed by slicer. And that's the bit I really struggle. 

 

[Dangar332 · Accepted solution]

Hi, @AntonioCarl 

Try Below Measure

Measure 5= 
	VAR Maintable = ALLSELECTED(
		'Table'[PRODUCT],
		'Table'[OPER_ID],
		'Table'[SERIALNO],
		'Table'[DURATION_S]
	)
	VAR RankTable = ADDCOLUMNS(
		Maintable,
		"Rank", RANK(
			DENSE,
			Maintable,
			ORDERBY(
				'Table'[DURATION_S],
				ASC
			),
			,
			PARTITIONBY(
				'Table'[PRODUCT],
				'Table'[OPER_ID]
			)
		)
	)
	VAR Minrank = MINX(
		FILTER(
			RankTable,
			'Table'[PRODUCT] = MIN('Table'[PRODUCT]) && 'Table'[OPER_ID] = MIN('Table'[OPER_ID])
		),
		[Rank]
	)
	VAR maxrank = MAXX(
		FILTER(
			RankTable,
			'Table'[PRODUCT] = MIN('Table'[PRODUCT]) && 'Table'[OPER_ID] = MIN('Table'[OPER_ID])
		),
		[Rank]
	)
	VAR _N = [Remove N Rows Value]
	VAR filtertable = FILTER(
		RankTable,
		[Rank] >= Minrank + _N && [Rank] <= maxrank - _N
	)
	VAR Avgtable_serialno = AVERAGEX(
		FILTER(
			filtertable,
			'Table'[PRODUCT] = MIN('Table'[PRODUCT]) && 'Table'[OPER_ID] = MIN('Table'[OPER_ID])
		),
		[DURATION_S]
	)
	VAR Avgtable_operlevel = AVERAGEX(
		FILTER(
			filtertable,
			'Table'[PRODUCT] = MIN('Table'[PRODUCT]) && 'Table'[OPER_ID] = MIN('Table'[OPER_ID]) && 'Table'[SERIALNO] = MIN('Table'[SERIALNO])
		),
		[DURATION_S]
	)
	VAR _Duration = IF(
		ISINSCOPE('Table'[SERIALNO]),
		Avgtable_operlevel,
		Avgtable_serialno
	)
	VAR _Hours = INT(_Duration / 3600)
	VAR _Minutes = INT(MOD(
		_Duration - _Hours * 3600,
		3600
	) / 60)
	VAR _Seconds = MOD(
		MOD(
			_Duration - _Hours * 3600,
			3600
		),
		60
	)
	VAR result =
	IF(
		_Duration <> 0,
		FORMAT(
			_Hours,
			"00"
		) & ":" & FORMAT(
			_Minutes,
			"00"
		) & ":" & FORMAT(
			_Seconds,
			"00"
		)
	)
	RETURN
		result

You can Download File from below

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AntonioCarl]

@Dangar332 omg, thank you, almost there. I've tried to add sum on Product level of those averagex on oper level but can't make it work. 

Could you please help me one more time? 
Thanks what I what to achieve:

[AntonioCarl]

@Dangar332  or could someone help?

[Dangar332 · Accepted solution]

Hi, @AntonioCarl 

Try below measure

AVG Duration without n rows = 
VAR _TempTable =
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[PRODUCT],
            'Table'[OPER_ID],
            'Table'[SERIALNO],
            'Table'[DURATION_S]
        ),
        "RANKING", RANKX(
            FILTER(
                'Table',
                EARLIER('Table'[PRODUCT]) = 'Table'[PRODUCT] &&
                EARLIER('Table'[OPER_ID]) = 'Table'[OPER_ID]
            ),
            'Table'[DURATION_S],
            ,
            ASC,
            DENSE
        )
    )

VAR _MinMaxRanking = 
    ADDCOLUMNS(
        _TempTable,
        "MinRanking", 
        MINX(
            FILTER(
                _TempTable,
                [PRODUCT] = EARLIER([PRODUCT]) &&
                [OPER_ID] = EARLIER([OPER_ID])
            ),
            [RANKING]
        ),
        "MaxRanking", 
        MAXX(
            FILTER(
                _TempTable,
                [PRODUCT] = EARLIER([PRODUCT]) &&
                [OPER_ID] = EARLIER([OPER_ID])
            ),
            [RANKING]
        )
    )

VAR _N = [Remove N Rows Value]
var Maintable =ALLSELECTED('Table'[PRODUCT],'Table'[OPER_ID],'Table'[SERIALNO],'Table'[DURATION_S])
var RankTable = ADDCOLUMNS(Maintable,"Rank",RANK(DENSE,Maintable,ORDERBY('Table'[DURATION_S],asc),,PARTITIONBY('Table'[PRODUCT],'Table'[OPER_ID])))
var Minrank =MINX(FILTER(RankTable,'Table'[PRODUCT]=MIN('Table'[PRODUCT]) && 'Table'[OPER_ID]=MIN('Table'[OPER_ID])),[Rank])
var maxrank = MAXX(FILTER(RankTable,'Table'[PRODUCT]=MIN('Table'[PRODUCT]) && 'Table'[OPER_ID]=MIN('Table'[OPER_ID])),[Rank])
var filtertable = FILTER(RankTable,[Rank]>=Minrank+_N && [Rank]<=maxrank-_N)
var Avgtable_serialno = AVERAGEX(FILTER(filtertable,'Table'[PRODUCT]=MIN('Table'[PRODUCT]) && 'Table'[OPER_ID]=min('Table'[OPER_ID])),[DURATION_S])
var Avgtable_operlevel = AVERAGEX(FILTER(filtertable,'Table'[PRODUCT]=MIN('Table'[PRODUCT]) && 'Table'[OPER_ID]=min('Table'[OPER_ID])&& 'Table'[SERIALNO]=MIN('Table'[SERIALNO])),[DURATION_S])
VAR _Filtered = 
    FILTER(
        _MinMaxRanking,
        [RANKING] >= [MinRanking] + _N && [RANKING] <= [MaxRanking] - _N
    )

VAR _AveragePerSerialNumber= 
    SUMMARIZE(
        _Filtered,
        [PRODUCT],
        [OPER_ID],
        [SERIALNO],
        "@AVG", AVERAGEX(
            FILTER(
                _Filtered,
                [PRODUCT] = EARLIER([PRODUCT]) &&
                [OPER_ID] = EARLIER([OPER_ID]) &&
                [SERIALNO] = EARLIER([SERIALNO])
            ),
            [DURATION_S]
        )
    )

-- Sum on Product and Oper_id columns
VAR _Level1and2 = 
    SUMX(
        SUMMARIZE(
            _AveragePerSerialNumber,
            [PRODUCT],
            [OPER_ID],
            "@AVGPerOperId", AVERAGEX(
                FILTER(
                    _AveragePerSerialNumber,
                    [PRODUCT] = EARLIER([PRODUCT]) &&
                    [OPER_ID] = EARLIER([OPER_ID])
                ),
                [@AVG]
            )
        ),
        [@AVGPerOperId]
    )

-- Sum on serial number column
VAR _Duration = 
    IF(
        HASONEVALUE( 'Table'[SERIALNO] ), 
        Avgtable_operlevel,
        _Level1and2
    )

VAR _Hours = INT( _Duration / 3600 )
VAR _Minutes = INT( MOD( _Duration - _Hours * 3600, 3600 ) / 60 )
VAR _Seconds = MOD( MOD( _Duration - _Hours * 3600, 3600 ), 60 )

VAR _Result = 
    IF(
        _Duration <> 0,
        FORMAT(_Hours, "00") & ":" & FORMAT(_Minutes, "00") & ":" & FORMAT(_Seconds, "00")
    )

RETURN 
    _Result

Download File from Below

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AntonioCarl]

@Dangar332 Thank you! I fighted with it long time and coudn't make it work. Now I need to analyze why my measure did not work and what you have changed to make it work. 

[Dangar332]

Hi, @AntonioCarl 

Enjoy your day 🙂 

[Dangar332]

Hi, @AntonioCarl 

Can you provide Data ,It's hard to predict from code.

[AntonioCarl]

Since I can't add file I needed to upload it on Dropbox. Here's link: https://www.dropbox.com/scl/fi/ysdvdw1ejtmqekj0z653y/example.pbix?rlkey=xjnjmlvzvzoewb46ubglg1avz&st...