Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
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 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"
datesold to partner code invoice quantity 
1-11-22abc12320 
2-11-22abc45680 
''..........''"..........""........" 
  • 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

5 Replies

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

    Hi Anonymous 

     

    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

     

     
    • Anonymous's avatar
      Anonymous
      Not applicable
      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