Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

dax

this is count cal when -ve retailing is included.

p3m =
var a= MAX('PSR'[Month])-2
var b =CALCULATE(DISTINCTCOUNT('PSR'[Customer_Code]),'PSR'[Month]>=a && 'PSR'[Month]<=MAX('PSR'[Month]))
return
b
this is the formula for the above visual. It calculates distinct count of customers where sept=sept+aug+july, aug= aug+july+june and so on.
in excel pivot it count is different it is 24225 for sept and that is the count i want.
https://drive.google.com/drive/folders/1i8pY-97Y7r7alnWXvQeXEjqarzGAnsdS?usp=drive_link data link.

Ahmedx 
Need Help

17 Replies

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    2 years ago

    HI Anonymous 
    Please do the following steps:
    Create new measure:

     

    Cust>0 = CALCULATE(COUNTROWS(Customer),FILTER(Customer,Customer[Retailing]>0))
     
    Create new measure 

    Cummuative =
    Var N= MAX(Customer[Month])-2
    Var A=CALCULATE(Customer[Cust>0],'Customer'[Month]>=N && 'Customer'[Month]<=MAX('Customer'[Month]))
    Return A

     

     

    I hope I answered your question.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      works 100% just 1 condition that i need is sum(retailing) and then distinctcount of customers. i got this result earlier also it is not wrong just after sum of retailing is applied then the count comes out to be different. ALso im not able to add this "sum of retailing" in the above measure

      • Uzi2019's avatar
        Uzi2019
        Community Champion
        2 years ago

        Hi Anonymous 
        Can you give me example by smapled data how you want Sum (retailing) to be added??
        On which condition ?
        or is it different measure or what?
        Initially you want the count(cust) whose retailing >0 right. then you want 3 months count together.

        Please mention the condition properly

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    that is the distinct count of all months and in the provided dataset.the data is only of 7,8,9th month so total distinct count was 24225. and this should be in the month value 9

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    =COUNTIF(E10:E24590,">0")