Problem with segmentation / grouping up data

[Anonymous]

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!

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

[Ashish_Mathur]

Hi,

Try this measure

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

Hope this helps.

[v-jiascu-msft]

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,

[Anonymous]

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. 

[v-jiascu-msft]

Hi @Anonymous,

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

Best Regards,