Forum Discussion

quickbi's avatar
quickbi
Icon for Helper II rankHelper II
2 years ago
Solved

How to create a percentage from table

Hi all, this is my first post, and thanks for your help.   I'm creating a cohort table, where I have the cohort_date on the rows and the created_at on the columns. I'm counting customer_id as the ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, 

    Thanks for the solution johnbasha33  provided, and i want to offer some more information for user to refer to.

    hello quickbi , based on your description, you can refer to the follwing solution.

    Sample data 

    The measure is the same as yours.

    You can create a new measure.

    MEASURE =
VAR a =
    CALCULATE (
        MIN ( cohort[created_at] ),
        ALLEXCEPT ( cohort, cohort[cohort_date], cohort[created_at].[Year] )
    )
VAR b =
    CALCULATE (
        [Customer Cohort],
        ALLSELECTED ( cohort ),
        cohort[cohort_date] IN VALUES ( cohort[cohort_date] ),
        EOMONTH ( cohort[created_at], 0 ) = EOMONTH ( a, 0 )
    )
RETURN
    DIVIDE ( [Customer Cohort], b )

    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.

     

quickbi's avatar
quickbi
Icon for Helper II rankHelper II
2 years ago

Hi johnbasha33 

 

Thanks for your answer, unfortunaley I did not had the result.

Percentage = DIVIDE([Customer Cohort],
    CALCULATE([Customer Cohort], ALLEXCEPT(cohort,cohort[cohort_date])), 0)
Customer Cohort = COUNT(cohort[customer_id])
 
table structure:

 

 thanks!!!!!
 
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, 

Thanks for the solution johnbasha33  provided, and i want to offer some more information for user to refer to.

hello quickbi , based on your description, you can refer to the follwing solution.

Sample data 

The measure is the same as yours.

You can create a new measure.

MEASURE =
VAR a =
    CALCULATE (
        MIN ( cohort[created_at] ),
        ALLEXCEPT ( cohort, cohort[cohort_date], cohort[created_at].[Year] )
    )
VAR b =
    CALCULATE (
        [Customer Cohort],
        ALLSELECTED ( cohort ),
        cohort[cohort_date] IN VALUES ( cohort[cohort_date] ),
        EOMONTH ( cohort[created_at], 0 ) = EOMONTH ( a, 0 )
    )
RETURN
    DIVIDE ( [Customer Cohort], b )

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.

 

  • quickbi's avatar
    quickbi
    Icon for Helper II rankHelper II
    2 years ago

    thank you very much, it works!!!