cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Problem with segmentation / grouping up data

Hello,

 

I am using powerpivot to analyse data for an internship. I have an normal dataset with one factstable with all the orders, one costumer table with the costumer names, country etc. and one table with the dates. 

 

I wanted to segmentation every costumer based on the revenue that the made each year. So the costumer segmentation has to be flexible each year. I want to make three groups: >1.000.000 = high, between 250.000 and 1.000.000 is mid and <250.000 is low. 

 

Now this is the problem: I can not make a segmentation that groups the data for each year. When I make a pivot table and put the revenue in the measure, it shows the right revenue. But the customer is in the wrong segmentation when I put the segmentation in the rows or filter and I have years (date) in the columns. 

 

I have tried to make a calculated column in the customers table, but that gives me the problem above. I first added a column that gave the revenue per costumer sumx(relatedtable)revenue. Then I added another calculated column that gave the segmentation based on the value of the previous column. The result gave the problem mentioned above. 

 What am I doing wrong that the costumers don't go in the right segmentation. I must use it in the rows or filter context in a pivot table.

 

Thank you a lot for your help!

 

 

5 REPLIES 5
v-jiascu-msft
Microsoft
Microsoft

Hi @Anonymous,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ashish_Mathur
Super User III
Super User III

Hi,

 

Try this measure

 

=IF(HASONEVALUE(Data[Customer]),IF([Revenue]<250000,"Low",IF([Revenue]<=1000000,"Medium","High")),BLANK())

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-jiascu-msft
Microsoft
Microsoft

Hi @Anonymous,

 

Can you share a sample, please? Please mask the sensitive parts.

I think the calculated column is a good idea. It provides the context. So I'm stuck here without a sample.

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-jiascu-msft

 

Well the problem is that all the columns data is in Dutch, so that makes it hard to read for you. Secondly, the company that doens't allow me to share the data. 

 

I shall make my question more accurate. I want to make a segmentation in my costumers table from the data of my salestable. The two tables have a one to many relationship. I can get the total sales per custumer (just a normal sumx) in a calculated colum in my costumers table. I can make a formula that puts the costumer in groups (if or switch function) but these don't respond correctly when I put the segmentation in the pivottable (as a row). If for example, a costumer has 700k revenue, it still goes in the segmentation big (>1.000.000 revenue). I think this is because my costumers table has a "one" relationship. I can't make a formula that ignores the relationship or something, that it puts the costumer in the right segementation.

 

Unfortunalty I cannot put a picture in this massage, what would have made it a lot easier to understand.

 

Thank you for your help. 

Hi @Anonymous,

 

I attached a demo in the attachment. Can you point out what the issue is?

Problem-with-segmentation-grouping-up-data

 

Best Regards,

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.