Converting dynamic measure to category/ flag/ column

[Anonymous]

Hi,

I have two tables

a. Customer-Month which contains Balance for each customer for evry month

b. Customer-Month-FeesType which contains Fee for each customer for each month and fees type

I have created a dynamic measures which calulates the %Fees= sum(Fees)/sum(Balance) and bucketed then into bands like "<0.01", "0.01-0.02" and so on..

The reason I created dymanic measure is, user can filter for any month or fees type and accordingly the %fee and corresponding bands should get re-calculated. At the end I want to show distribution of customers across those bands based on any type of filters.

It works at an overall level (i.e. when no filter is applied) but as soon as I apply filter for a month, it fails.

Can someone help me with it.

Regards,

Kavita Sakure

+61 448516662

[technolog · Accepted solution]

To address the issue of calculating dynamic percentage fees and categorizing them into bands while respecting filters, we need to ensure the measure and the categorization logic are flexible enough to handle the context changes introduced by the slicers. Here’s a step-by-step solution:

Step 1: Create the Dynamic Measure for %Fees
Create a measure to calculate the percentage fee dynamically based on the filters applied.

PercentageFees =
DIVIDE(
SUM('Customer-Month-FeesType'[Fees]),
SUM('Customer-Month'[Balance]),
0
)
Step 2: Create a Measure for Banding
Next, create a measure to categorize the calculated %Fees into the desired bands. This measure will dynamically adjust based on the context provided by the filters.

FeeBand =
SWITCH(
TRUE(),
[PercentageFees] < 0.01, "<0.01",
[PercentageFees] >= 0.01 && [PercentageFees] < 0.02, "0.01-0.02",
[PercentageFees] >= 0.02 && [PercentageFees] < 0.03, "0.02-0.03",
[PercentageFees] >= 0.03 && [PercentageFees] < 0.04, "0.03-0.04",
">=0.04"
)
Step 3: Create a Table to Display Distribution of Customers
To display the distribution of customers across these bands, create a calculated table or use the existing data structure with the new dynamic banding measure.