Forum Discussion

capibaraya's avatar
capibaraya
Regular Visitor
8 years ago
Solved

month over base change

Hi! I'm looking for the solution of the following task. 

 

I have table with columns: period, client id (those who have used service this month), month of first visit. I have a histogram for visits with period as X and client id count as Y. Also I have a slicer with the month of first visit. So I can choose those clients, who had used service for the first time in May, and see how many of them used it again in July, August, October etc. Now I need to add a line (to calculate a measure) to show, which percentage of May-clients returned back in  July, August, October etc. So I have to choose the base month for calculation depending on the value of 'month of first visit' choosen on slicer. I'll be thankfull if someone can help me. 

 

mon  count desired column

jan    0      0%

feb    0      0%

mar   0      0%

apr   100   100%

may  80     80%

jun    71     71%

july   10     10%

aug   2       2%

sep   0        0%

oct    0        0%

nov   0        0%

dec   0        0%

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi capibaraya,

     

    Please refer to below measures:

    count client per month = DISTINCTCOUNT('Test Tb1'[Client id])
Count all in selcted month = CALCULATE(DISTINCTCOUNT('Test Tb1'[Client id]),ALL('Test Tb1'[Month]))

percentage = [count client per month]/[Count all in selcted month]

     

    Best regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi capibaraya,

     

    Please refer to below measures:

    count client per month = DISTINCTCOUNT('Test Tb1'[Client id])
Count all in selcted month = CALCULATE(DISTINCTCOUNT('Test Tb1'[Client id]),ALL('Test Tb1'[Month]))

percentage = [count client per month]/[Count all in selcted month]

     

    Best regards,

    Yuliana Gu