Count customers from a group, while removing filter on said group

[KPau]

Hi everyone,

I've been trying to figure this one out, unsuccessfully.

I have a facts table which countains a billing amount per Customer and per month. Months are pivoted so that billing amounts are stored in each column for each month. A customer belongs to an internal classification, which can change overtime (Customer 1 may belong to group A in january but be attached to group B in february). My table looks like this :

Group	Customer_ID	Billing202305	Billing202304	...	Billing202205	...	flag last year
A	1	0	0		100		1
B	1	120	110		0		1
A	2	20	20		0		0
A	3	30	30		30		1
B	4	0	0		500		1

I need to figure out, for a given month, how many customers were billed said month and, AMONG THEM, how many customers are still billed exactly one year later. This will allow me to calculate a rate : (number of billed customers same month last year - number of billed customers this month) / number of billed customers same month last year.

The field "Flag last year" equals 1 if a customer was billed the same month a year before, regardless of the group they belong to.

So, in my example, my output should be this :

Month	Number of billed Customers in 2022-05	Among them, number of customers still billed in 2023-05	Rate
2023-05 vs 2022-05	3 (id 1;3;4)	2 (id 1 and 3)	33%

I managed to get this part to work by defining variables, which count distinct Customers for each month, and then using a SWITCH function to determine which variable is to be displayed in the table.

My problem now is that users need to filter by Group. And, as I mentioned, a Customer can change groups overtime. However, users want to count Customers, regardless of their groups.

In my example, this means that if a user filters on group A, the diplayed rate should count customer 1 on both 2022-05 and 2023-05 periods, not only on 2022-05.

How to get there ? I mean, if they select a group, my table gets filtered and the measure cannot take into consideration my second row anymore. If I use a REMOVEFILTERS function, I loose the information of the group that was filtered on.
So here is how my measure should work : "you filtered on group A : here is the list of customers that belong to that group. Among these customers, here is how many are still billed one year later".

I figured perhaps I should use a SUMMARIZE funciton but I'm not confident on that and still wouldn't know how to build the measure.

I hope this makes sense, thanks a lot for your help

[v-zhangti · Accepted solution]

Hi, @KPau 

Is your source data as shown in the diagram?

Measure:

Number of billed Customers in 2022-05 = CALCULATE(COUNT('Table'[Customer_ID]),FILTER(ALL('Table'),[Date]=DATE(2022,5,1)))

Number of customers still billed in 2023-05 = 
Var _table=CALCULATETABLE(VALUES('Table'[Customer_ID]),FILTER(ALL('Table'),[Date]=DATE(2022,5,1)))
Return
CALCULATE(COUNT('Table'[Customer_ID]),FILTER(ALL('Table'),[Date]=DATE(2023,5,1)&&[Customer_ID] in _table))

Rate = DIVIDE([Number of billed Customers in 2022-05]-[Number of customers still billed in 2023-05],[Number of billed Customers in 2022-05])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KPau]

Hi @v-zhangti ,

Thanks for your help. This method indeed worked fine in my Power BI Desktop, but not in Power BI Service.

My source data does not quite fit your screeshot, as it is pivoted on the date column. It looks like this instead :

But by adapting the formula a bit (using Billing202205 > 0 instead of date=2022-05), it worked like a charm... in Power BI Desktop.

However, when published to the service, the report will show a "visual has exceeded available resources" error. I simplified the dataset for my example but I have many, many customers that very frequently change billing groups, and I have to retrieve data for 36 months... 

I tried to create anoter facts table which showed Customers and Billing amounts (still with month in columns), and then implement an N-N relationship between my current facts table and this one, on customer_id, but resources are still exceeded that way. 

Is this request too heavy for Power BI to process or should I search for a work-around ?

Thanks again for your help

Regards