Grouping data based on two level conditions

[ugux]

I have a Data modell, which works fine. Data table, Customers, products, dates…etc…Now I got a special request
They want to see a new kind of grouping, which have 2 levels.
1st - not all of customers will be grouped, just special ones.
2nd - we don't want to see all sales data by customer, only for special product types. So for example Customer 11 - just produt A and C, Customer 21 only Product B...  like in picture below

How Can I do this?

Final result would be : Customer_G1 - sum of sales for that 3 customers only the products types signed with "x"

Thank you

[Jihwan_Kim]

Hi, @ugux 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

The lower three visuals are using the conditional calculated measure.

The measures are in the sample pbix file.

https://www.dropbox.com/s/67jt8i10aa0zi96/ugux.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[ugux]

Hi Guys,

Thank you for your support. I checked the pbix file, it would be fine, but I have 10 product type, and more than 300 Customers. So it should be dynamic somehow, we don't want to write 300 conditions into Switch.

Is it helps, if I reshape my conditions table into normal table look instead of Matrix?

I mean 3 columns like this:

Groups | Customer Nr | Product Type

...

...

Thank you

[Jihwan_Kim]

Hi, @ugux 

I am not sure how your all other tables, other measures, and relationships with other tables look like.

If you have more than 300 customers and 10 product types, and if the only problem is to solve this question, I definitely agree with you, and reshaping your fact table really helps a lot.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[ugux]

Hi,

My fact tale is an other story, there is no problem. I speak about reshaping regarding my Conditions table. 

So the question is still, how can I reach a sum sales Value based on my conditions...

example: Group1 = 200.000 (which comes from 30 Customers, and each customers have different product mix what I want to sum) It is possible, that they bought all kind of products, but I want to summarize only which are connected to the customer in my conditions table.

thank you

[Jihwan_Kim]

Please share your sample pbix file's link here.

[ugux]

Hi Kim,

I used your first example file, and I modified tables like it is exist in my real model.

I added 2 Conditions tables version 1 and 2.  (no visuals now) only the tables and connections.

Now you can see that not all of products and customers are involved in this Grouping task.

Here is the File:

https://drive.google.com/file/d/1Q7mplGacDYcWisGrkTBHGLVBdyTNAaqU/view?usp=sharing

Thank you

[Jihwan_Kim · Accepted solution]

Hi, @ugux 

Thank you for sharing.

Please check the link down below.

It seems working. However, I am not quite convinced to myself whether it is the best solution or the worst solution. Because the measure is using Treatas function two times in the same measure.

Please check whether it suits your real model.

https://www.dropbox.com/s/vaf19ug4l029g17/ugux_v2.pbix?dl=0 

Thank you.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[ugux]

Hi Kim,

It's fantastic, so simple in 1 row measure 🙂 and working. Now I must learn what treatas doing exactly.

Thank you so much.

Have a nice day

[amitchandak]

@ugux , you might have create a measure using switch

like

Switch ( True() ,

max(product[product]) = "Product A" && max(Customer[Customer]) = "X" ,

// Add others

)