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

[_mathew_]

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!

[Anonymous · Accepted solution]

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