Forum Discussion

SachinNamdeo-20's avatar
SachinNamdeo-20
Icon for Helper II rankHelper II
3 years ago
Solved

Calculate Effective Dealer

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 d...
  • pi_eye's avatar
    pi_eye
    3 years ago

    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's avatar
SachinNamdeo-20
Icon for Helper II rankHelper II
3 years ago

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's avatar
pi_eye
Icon for Resolver IV rankResolver IV
3 years ago

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's avatar
    SachinNamdeo-20
    Icon for Helper II rankHelper II
    3 years ago

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

    dealer invoice quantityeffective dealer  
    a10   
    b202b  
    c28c  
    d30   

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

    • pi_eye's avatar
      pi_eye
      Icon for Resolver IV rankResolver IV
      3 years ago

      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