Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count class changes and abandon rate

Hi guys I really don't know how to name this question but here is what im trying to do.

I have stores labeled by a class A+, A, B, C and N every time we open a new store we name them N every month it changes from N to any other class based on their results, first I want to know if I can count this jumps from N-C or N-B,etc. In the first month, after three months of this change I need to see if they still at a good class. and point the ones that are "abandoned" the ones that are C class and the ones that are good A, A+.

Example:

Total of N stores that changed to C in their first month of operation =1 juanito's

Total of N stores abandoned "C- class" in their third month of operation = 3, Pedro's Chucho's and Pepe´s stores

 

Thanks a lot for the help! I really appreciate it.

 

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous 

    Please refer to the following measures.

     

    One_month change = var a=CALCULATE(MAX('Table'[Mon_num]),'Table'[Level]="N")
var b=IF(a<>BLANK(),CALCULATE(MAX([Level]),'Table'[Mon_num]=a+1),CALCULATE(MAX([Level]),'Table'[Mon_num]=MIN('Table'[Mon_num])))
return IF("N" in VALUES('Table'[Level])&&b="C",1,0)

Three_month change = var a=CALCULATE(MAX('Table'[Mon_num]),'Table'[Level]="N")
var b=IF(a<>BLANK(),CALCULATE(MAX([Level]),'Table'[Mon_num]=a+3),CALCULATE(MAX([Level]),'Table'[Mon_num]=MIN('Table'[Mon_num])+2))
return IF("N" in VALUES('Table'[Level])&&b="C",1,0)
    One_month counts = SUMX(VALUES('Table'[Name]),[One_month change])

three_month counts = SUMX(VALUES('Table'[Name]),[Three_month change])

     

    Output

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous 

    You can refer to the following measures

    1.Create two measures to calculate the fist month and the third month level

    One_month change = var a=CALCULATE(MAX('Table'[Mon_num]),'Table'[Level]="N")
var b=CALCULATE(MAX([Level]),'Table'[Mon_num]=a+1)
return b

Three_month change = var a=CALCULATE(MAX('Table'[Mon_num]),'Table'[Level]="N")
var b=CALCULATE(MAX([Level]),'Table'[Mon_num]=a+3)
return b

    2.Then create two other measures to count the fist month counts and the third month counts

    One_month counts = COUNTROWS( FILTER(VALUES('Table'[Name]),[One_month change]="C"))

three_month counts = COUNTROWS( FILTER(VALUES('Table'[Name]),[Three_month change]="C"))

    Then display the count measures

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello Yolo Zhu I tried to use your commands and they work but I think I just messed up a bit and here is why I thinks so. Not all workers starts from N class because they have been working longer from the data I have so they can start with other class, here is an example.

       

      Here Aaron appears as he had his first month store as C but the thing is that he was never in the N category 

      So in the mayority of cases I have data like Aaron's. What can I do?

       

      Thanks a lot Anonymous  for the help

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous 

        I don't quite understand how to define the first month of store opening, at first I understood that the first month after a certain store is marked N is the month in which the store is open, but you said this Aaron store, then his first month should be January?

        Refer to the above understanding, you can refer to the following two measures.

        One_month change = var a=CALCULATE(MAX('Table'[Mon_num]),'Table'[Level]="N")
var b=IF(a<>BLANK(),CALCULATE(MAX([Level]),'Table'[Mon_num]=a+1),CALCULATE(MAX([Level]),'Table'[Mon_num]=MIN('Table'[Mon_num])))
return b

Three_month change = var a=CALCULATE(MAX('Table'[Mon_num]),'Table'[Level]="N")
var b=IF(a<>BLANK(),CALCULATE(MAX([Level]),'Table'[Mon_num]=a+3),CALCULATE(MAX([Level]),'Table'[Mon_num]=MIN('Table'[Mon_num])+2))
return b

         I  add a number field [Month_num  in the sample data you provided

         

        Best Regards!

        Yolo Zhu