Calculate Effective Dealer

[SachinNamdeo-20]

I want to calculate only   count of  Effective dealer,
Effective dealer condition is :- Which dealer has  previous 3 month avarage sales  is greater than 40 is satisfied the condition of effective dealer please help to provide dax query for this 
" 3 Month avarage sale measure is :- Table name- cube invoice

    3mth avarage sales = CALCULATE(SUM(CUBE_INVOICE[INVOICE QUANTITY]),DATESINPERIOD(CUBE_INVOICE[D         DATE.FULLDATE],LASTDATE(CUBE_INVOICE[D DATE.FULLDATE]),-3,MONTH))/3"

date	sold to partner code	invoice quantity
1-11-22	abc123	20
2-11-22	abc456	80
''..........''	".........."	"........"

[pi_eye]

Hi @SachinNamdeo-20 

where are you using this measure?

If you have the average over 3 months calculated, you can just apply an if statement:

3 MONTH AVERAGE = CALCULATE(SUM(invoice[INVOICE QUANTITY]),DATESINPERIOD(invoice[DATE],LASTDATE(invoice[DATE]),-3,MONTH))/3

Is Effective? = IF([3 MONTH AVERAGE]>=40,"Y","N")

Recreation of data:

HTH,

Pi

[SachinNamdeo-20]

I just want to show the actual count of effective dealer in card so i want just one value that is actual count of total effective dealer so thank you for the suggestion but i cant show the value in form of table

[pi_eye · Accepted solution]

So you need to count the effective Dealers for a given selection? 

Try something like

Effective Count = SUMX( VALUES( invoice[DEALER]), IF (  [3 MONTH AVERAGE] >= 40, 1))

 Where :

 [3 MONTH AVERAGE] = CALCULATE(SUM(invoice[INVOICE QUANTITY]),DATESINPERIOD(invoice[DATE],LASTDATE(invoice[DATE]),-3,MONTH))/3

This will aggregate up to the total effective dealers:

In a table with dealer:

In object on it's own:

If this is not what you need, can you illustrate maybe using excel just to show what the end result what look like?

Thank you

Pi

[SachinNamdeo-20]

"HEY ! now i want to calculate effective dealer sales ,can you healp me data is same for this

dealer	invoice quantity	effective dealer
a	10
b	202	b
c	28	c
d	30

Effective dealer measure was previous one Now i want a  calculated measure which can calculate effective dealer total sales"

[pi_eye]

Hi @SachinNamdeo-20 

You can try replacing the count in the expression with the sum:

Effective Dealer Total = SUMX( VALUES( invoice[DEALER]), IF (  [3 MONTH AVERAGE] >= 40, sum(invoice[INVOICE QUANTITY])))

Pi