cancel
Showing results for
Did you mean:
Helper II

## Measures That have Different Behaviour Between Category

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

1 ACCEPTED SOLUTION
Microsoft

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Microsoft

Hi @andrehawari,

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

Announcements

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors