Measures That have Different Behaviour Between Category

[andrehawari]

Hi

I have the following table below. 

Basically I want to create the measure that have following behaviour, 

IF, Category=A, then average(Sales) else

IF Category=B, then sum(Sales).

I have created the following dax query, but it gave me a wrong result

IF (   max(Table1[Category])=A, AVERAGE(Table1[Sales]), sum(Table1[Sales]))

Thanks

[v-danhe-msft]

Hi @andrehawari,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @andrehawari,

Based on my test, you could refer to below formula:

Measure = IF(MAX('Table1'[Category])="A",CALCULATE(AVERAGE(Table1[Sales]),ALL(Table1)),CALCULATE(SUM(Table1[Sales]),ALL(Table1)))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[aaronwatt]

This should give you the correct results 

SalesMeas =
MAXX (
 KEEPFILTERS ( VALUES ( 'Table1'[Category] ) ),
 CALCULATE (
 IF (
 GROUPBY ( Table1, Table1[Category] ) = "A",
 AVERAGE ( Table1[Sales] ),
 SUM ( Table1[Sales] )
 )
 )
) 

Aaron