Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to use a measure to categorize clients based on their spending?

I have a problem in Power BI Desktop that I just cannot get my head around. I have a dataset with health care data (invoices) that I analyze for local government. We have a large number of suppliers and a list of products (treatments) that they can supply. I have included screenshot down below of a example dataset with a limited number of rows and fields.

 

Power BI vraag.PNG


I would like to compare suppliers based on the amounts that they charge per client for the same product. In addition to an average price per client, I would like to categorize clients in “cost categories”. A client that costs €9,000 a year, for instance, should be in the “€5,000 – €10,000” category. I’d then like to visualize the results in a column chart with the cost categories on the horizontal axis and the number of (unique) clients on the vertical axis. Through that, I would be able to determine whether all the clients cluster around the average cost per client or whether there are outliers that distort the average.

At first, I used a calculated table do the analysis. This works, to some extent, but the result is non-filterable. It often happens that clients use multiple products from multiple suppliers. Overall, a client that costs €6,000 a year at supplier A and €3,000 a year at supplier B should be in the “€5000 – €10,000” category. When I select supplier B, however, he should drop to the “<€5,000” category.

Ideally, I’d like to use measures instead of a calculated table. Power BI, however, does not accept measures as the horizontal axis of a column chart. As a workaround, I created table containing the upper limits of each cost categories (€5,000, 10,000, …). I then tried to create a measure that counts the number of clients per category, but I cannot get it to work since there is no relationship between the tables. As of right now, I’m honestly out of ideas.

Could anybody help me out? (:

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I solved it!

 

Clients per cost category = 
SUMX(
FILTER(
GROUPBY(
ADDCOLUMNS(
GROUPBY('Invoices'; 'Invoices'[Client ID];
"Amount"; SUMX(CURRENTGROUP(); 'Invoices'[Client ID]));
"Cost category"; ROUNDUP([Amount]; -3));
[Cost category]; 
"Costs"; SUMX(CURRENTGROUP(); [Amount]));
[Cost category] = FIRSTNONBLANK('Cost category'[Cost category]; 
'Cost Category[Cost category]));
[Costs])

View solution in original post

6 REPLIES 6
sanchit_1610
Helper I
Helper I

Hi 

 

I have a similar situation but not able to find what you did here. Can you please help me with my query here

https://community.powerbi.com/t5/Desktop/Partition-Measure-values-into-groups-and-change-dynamically...

 

Thanks

Sanchit

v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

Ideally, I’d like to use measures instead of a calculated table. Power BI, however, does not accept measures as the horizontal axis of a column chart. As a workaround, I created table containing the upper limits of each cost categories (€5,000, 10,000, …). I then tried to create a measure that counts the number of clients per category, but I cannot get it to work since there is no relationship between the tables. As of right now, I’m honestly out of ideas.

Could anybody help me out? (:

So you have generated the table which contains the category as you said, right? Why not create a calculate column directly in the original table using SWITCH function? Generally, you may create a column using DAX below:

Category = SWITCH(TRUE(), 'Table'[Price] < 5000, "< 5000", 'Table'[Price] >= 5000 && 'Table'[Price] <= 10000, "5000 - 10000")

Capture.PNG 

Then you can create a measure to calculate the count value.

Count = CALCULATE(COUNT('Table'[Client ID]), ALLEXCEPT('Table', 'Table'[Category]))

Community Support Team _ Jimmy Tao

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

 

Anonymous
Not applicable

@v-yuta-msft Thanks for the reply and my apologies for the late response.

 

The reason why I do not use a calculated column to calculate the category (anymore) is because a calculated column cannot do what I want it to do. Every client should be assigned to only one category, which should change accordingly with the suppliers and products selected. Overall, with all products and suppliers selected, client A should be in the 15000 - 20000 category (in your example file). When you select product A1, that should change to the <5000 category.

I have made quite a lot of progress on this issue myself in the meantime. Right now, I create a table that contains all possible cost categories.

 

Categories = 
GENERATESERIES(0; MAXX(
SUMMARIZE('Invoices'; 'Invoices'[Client ID]; "Costs"; SUM('Invoices'[Price])); 
[Costs]); 1000)

To that table, I add a measure that calculates the number of clients in each category.

 

Clients per category = 
SUMX(
SUMMARIZE(
CALCULATETABLE(
SUMMARIZE(Invoices; Invoices[Client ID]; 
"Category"; ROUNDUP(SUM(Invoices[Price]); -3));
FILTER(Invoices; 
CALCULATE(ROUNDUP(SUM(Invoices[Price]); -3); 
ALLEXCEPT(Invoices; Invoices[Client ID])) = 
FIRSTNONBLANK(Categories[Categories]; Categories[Categories])));
[Category]; 
"Clients"; DISTINCTCOUNT(Invoices[Client ID])); 
[Clients])

I have added the categories table and the clients per category measure to your pbix file, you can download it at https://www.dropbox.com/s/b33odufhnv5g0bk/Dynamic%20tables.pbix?dl=0.

 

The measure works great, until you start filtering by supplier or product code, due to the ALLEXCEPT statement. Without it, CALCULATE does not factor in the Client ID in its calculation. When I select a supplier or a product, however, I would like to see that the measures only selects the costs of that supplier/product in its calculation. That could mean adding the supplier/product columnt to ALLEXCEPT or replacing it with a more appropriate statement. I have considered using a number of IF statements like the one below, but I doubt that would actually work. There is no supplier that offers all products and vice versa, therefore filtering one inevitably means filtering the other.

 

IF(VALUES(Invoices[Supplier]) = VALUES(ALL(Invoices[Supplier])); ...; ...)

 

Could anybody help me out here? Is there an alternative to ALLEXCEPT that is more appropriate to use in this context?

Anonymous
Not applicable

I spend some more time on it and found a solution that works, most of the time. You can use ISFILTERED to check which columns are filtered and use IF statements to  create a separate script for every combination of filters. E.g. when you filter on supplier, you include supplier in the ALLEXCEPT command, on top of the Client ID. It is not very elegant but it works most of the time.

 

The only problem arises when you select multiple suppliers, but not all. Clients that visit two or more of the selected suppliers are now counted multiple times, once for every supplier. This happens because the supplier is in the ALLEXCEPT, in order to filter out suppliers that are not selected. Is it possible to filter out all suppliers that are not selected, without the distinction between the suppliers that are selected?

Anonymous
Not applicable

I solved it!

 

Clients per cost category = 
SUMX(
FILTER(
GROUPBY(
ADDCOLUMNS(
GROUPBY('Invoices'; 'Invoices'[Client ID];
"Amount"; SUMX(CURRENTGROUP(); 'Invoices'[Client ID]));
"Cost category"; ROUNDUP([Amount]; -3));
[Cost category]; 
"Costs"; SUMX(CURRENTGROUP(); [Amount]));
[Cost category] = FIRSTNONBLANK('Cost category'[Cost category]; 
'Cost Category[Cost category]));
[Costs])

@Anonymous Hi I also got a similar query, can you please elaborate what you did here.

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.