growth% bins to calculate client count

[sivaadityat]

Hello All,

I am looking for a solution in Power BI DAX for the following scenario.

Scenario: I have calculated the Client growth in measure and categorized by growth percentages e.g{(0 - 10%),(10 - 20%)}. Now, I would like to count the number of clients fall under the category.

Here category is created in MEASURE & Client name is in COLUMN(default in table)

Please help me to create a DAX for this scenario. Also, I need to visualize a bar graph with categories.

Thanks,

[123abc]

To count the number of clients falling into specific growth percentage categories and visualize this data in a bar graph in Power BI, you can follow these steps:

1. Create a measure to calculate the client growth percentage. Assuming you already have this measure, let's call it ClientGrowthPercentage.

2. Create a new measure to categorize the clients based on their growth percentages into bins. You can use the SWITCH function to create these categories. Here's an example DAX measure for categorizing clients into percentage bins:

ClientGrowthCategory = SWITCH ( TRUE (), [ClientGrowthPercentage] >= 0 && [ClientGrowthPercentage] < 10, "0 - 10%", [ClientGrowthPercentage] >= 10 && [ClientGrowthPercentage] < 20, "10 - 20%", // Add more conditions for additional bins as needed "Other" // Default category for values outside defined bins )

In this example, we've defined two growth percentage categories (0 - 10% and 10 - 20%). You can add more conditions for additional bins as required.

1. Now, create a bar graph visualization in Power BI. Place the ClientGrowthCategory measure on the axis of the bar chart and use the COUNTROWS function to count the number of clients in each category. Create a new measure for this count, like so:

ClientCountByCategory = COUNTROWS ( FILTER ( YourTableName, [ClientGrowthCategory] = SELECTEDVALUE ( YourTableName[ClientGrowthCategory] ) ) )

Replace YourTableName with the name of your table.

1. Use the ClientCountByCategory measure as the values for your bar chart. This will display the count of clients in each growth percentage category in the bar chart.

2. Customize your chart as needed by adding labels, titles, and any other formatting options to make it visually appealing.

Now, when you select the growth percentage category in your bar chart, it will display the count of clients in that category, based on your categorization logic.

[sivaadityat]

Hi There, 

Thanks for the reply!

but, this solution is not working here: 

ClientCountByCategory = COUNTROWS ( FILTER ( YourTableName, [ClientGrowthCategory] = SELECTEDVALUE ( YourTableName[ClientGrowthCategory] ) ) )

SELECTEDVALUE is not accepting measure, it is expecting a column name.

Please let me know is there any alternate to resolve this issue.

Thanks

[123abc · Accepted solution]

Step 1: Create Growth Percentage Measure First, you need to create a DAX measure to calculate the growth percentage for each client. Assuming you have a measure named "Client Count" to count the number of clients, you can create a measure like this:

Growth Percentage = VAR CurrentCount = [Client Count] VAR PreviousCount = CALCULATE([Client Count], PREVIOUSMONTH('Date'[Date])) VAR Growth = IF(ISBLANK(PreviousCount), BLANK(), (CurrentCount - PreviousCount) / PreviousCount) RETURN Growth

This measure calculates the growth percentage for each client based on the current month's client count compared to the previous month. Adjust the date column ('Date'[Date]) to match your date column.

Step 2: Create Growth Percentage Categories Next, you need to create a measure that categorizes the growth percentages into bins. You can use a SWITCH function for this purpose:

Growth Category = VAR GrowthPercentage = [Growth Percentage] RETURN SWITCH( TRUE(), GrowthPercentage >= 0.1 && GrowthPercentage < 0.2, "10 - 20%", GrowthPercentage >= 0.2 && GrowthPercentage < 0.3, "20 - 30%", GrowthPercentage >= 0.3 && GrowthPercentage < 0.4, "30 - 40%", GrowthPercentage >= 0.4 && GrowthPercentage < 0.5, "40 - 50%", GrowthPercentage >= 0.5 && GrowthPercentage < 0.6, "50 - 60%", GrowthPercentage >= 0.6 && GrowthPercentage < 0.7, "60 - 70%", GrowthPercentage >= 0.7 && GrowthPercentage < 0.8, "70 - 80%", GrowthPercentage >= 0.8 && GrowthPercentage < 0.9, "80 - 90%", GrowthPercentage >= 0.9 && GrowthPercentage <= 1, "90 - 100%", "0 - 10%" )

This measure categorizes growth percentages into bins ranging from 0% to 100%. Adjust the bin ranges as needed.

Step 3: Create a Bar Chart Now, you can create a bar chart to visualize the client count by growth percentage categories.

• Place "Growth Category" on the Axis field of the bar chart.
• Place "Client Count" (or your actual client count measure) on the Values field of the bar chart and set it to count.

This will create a bar chart showing the count of clients in each growth percentage category.

Note: Make sure to adjust the measures and column names to match your data model and requirements. The above DAX measures and categories are just examples, and you can customize them based on your specific data and bin ranges.

If this not work then please try this:

Step 1: Create a Calculated Column for Growth Categories

Assuming your table is named "YourTableName," create a new calculated column like this:

ClientGrowthCategory = VAR GrowthPercentage = [Growth Percentage] RETURN SWITCH( TRUE(), GrowthPercentage >= 0.1 && GrowthPercentage < 0.2, "10 - 20%", GrowthPercentage >= 0.2 && GrowthPercentage < 0.3, "20 - 30%", GrowthPercentage >= 0.3 && GrowthPercentage < 0.4, "30 - 40%", GrowthPercentage >= 0.4 && GrowthPercentage < 0.5, "40 - 50%", GrowthPercentage >= 0.5 && GrowthPercentage < 0.6, "50 - 60%", GrowthPercentage >= 0.6 && GrowthPercentage < 0.7, "60 - 70%", GrowthPercentage >= 0.7 && GrowthPercentage < 0.8, "70 - 80%", GrowthPercentage >= 0.8 && GrowthPercentage < 0.9, "80 - 90%", GrowthPercentage >= 0.9 && GrowthPercentage <= 1, "90 - 100%", "0 - 10%" )

This calculated column will categorize each row based on the growth percentage.

Step 2: Create a Measure for Client Count by Category

Now, create a measure to count the clients by category:

ClientCountByCategory = COUNTROWS ( FILTER ( YourTableName, [ClientGrowthCategory] = SELECTEDVALUE ( YourTableName[ClientGrowthCategory] ) ) )

Step 3: Create the Bar Chart

Create a bar chart and place the "ClientGrowthCategory" column on the Axis field, and use the "ClientCountByCategory" measure as the Values field.

This should create a bar chart that displays the count of clients by growth percentage categories.

By creating the "ClientGrowthCategory" as a calculated column, you can use it for filtering and grouping in your visuals.