How to create a percentage from table

[quickbi]

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 value. The result is good, but I would like to have the percentage of each value as shown in the picture. Thank you very much.

[johnbasha33]

@quickbi 

To achieve this in DAX (Data Analysis Expressions), assuming you're working in Power BI or another tool that supports DAX, you can follow these steps:

1. First, you need to create a measure to count the number of customers in each cohort.
2. Then, create another measure to calculate the percentage of customers for each cohort.

Here's how you can write the DAX measures:

1. Measure to count the number of customers in each cohort:
```dax
CustomersCount = COUNTROWS(your_table)
```

2. Measure to calculate the percentage of customers for each cohort:
```dax
Percentage = DIVIDE([CustomersCount], CALCULATE([CustomersCount], ALLEXCEPT(your_table, your_table[cohort_date])), 0) * 100
```

In the above code:
- `[CustomersCount]` is the measure you created to count the number of customers in each cohort.
- `ALLEXCEPT(your_table, your_table[cohort_date])` removes all filters from your table except for the cohort date, ensuring that the calculation is done at the cohort level.

Make sure to replace `your_table` with the name of your actual table, and `cohort_date` with the name of your cohort date column.

Once you've created these measures, you can use them in your visualizations to display the cohort analysis with percentages.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[quickbi]

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!!!!!

[v-xinruzhu-msft · Accepted solution]

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]

thank you very much, it works!!!

[johnbasha33]

@quickbi 

It seems like you're trying to create a cohort analysis table where you're counting the number of customers in each cohort over time. To calculate the percentage of each value, you can divide each count by the total count in its respective cohort. Here's how you can do it:

1. Calculate the total count for each cohort.
2. For each value in the table, divide it by the total count of its cohort and multiply by 100 to get the percentage.

If you're using SQL, you can achieve this with a query similar to the following:

```sql
SELECT
cohort_date,
SUM(CASE WHEN DATE_TRUNC('month', created_at) = cohort_date THEN 1 ELSE 0 END) AS total_customers,
SUM(CASE WHEN DATE_TRUNC('month', created_at) = cohort_date THEN 1 ELSE 0 END)::float / COUNT(customer_id) * 100 AS percentage
FROM your_table
GROUP BY cohort_date
ORDER BY cohort_date;
```

This query will give you the cohort_date, total number of customers in each cohort, and the percentage of customers created in each cohort. Adjust the column and table names according to your schema.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!