topic Calculate the mean value based on n% of the maximum value of another variable in the table in DAX Commands and Tips

Calculate the mean value based on n% of the maximum value of another variable in the table

_mathew_ — Wed, 26 Jul 2023 18:04:00 GMT

Hi all,

Based on the table, I want to determine the average value of Value2 (channel "y") per product in the range defined as n% (e.g. 30%) of the maximum value of Value1 (channel "x"). How can I do it with a DAX measure?

Product	Subproduct	Channel	Value1	Value2
AB	1	x	3728
AB	1	y		22
AB	2	x	4117
AB	2	y		57
ABA	3	x	5379
ABA	3	y		121
ABA	4	x	1202
ABA	4	y		46
ABA	8	x	4888
ABA	8	y		78
AC	11	x	7635
AC	11	y		32
AC	14	x	2583
AC	14	y		61
AC	163	x	1792
AC	163	y		59

Thanks for sharing your ideas!

Re: Calculate the mean value based on n% of the maximum value of another variable in the table

Anonymous — Fri, 28 Jul 2023 07:13:26 GMT

Hi _mathew_,

You can try to use the following measure formula to get the difference between different sub categories:

formula = VAR v1 = CALCULATE ( MAX ( 'Table'[Value1] ), FILTER ( ALLSELECTED ( 'Table' ), [Channel] = "x" ), VALUES ( 'Table'[Product] ) ) VAR v2 = CALCULATE ( AVERAGE ( 'Table'[Value2] ), FILTER ( ALLSELECTED ( 'Table' ), [Channel] = "y" ), VALUES ( 'Table'[Product] ), VALUES ( 'Table'[Subproduct] ) ) RETURN DIVIDE ( v2, v1 )

Regards,

Xiaoxin Sheng