VAR of measure (shares) to parent category measure (shares

[kostask]

Hi guys!

It looks pretty sophisticated and I'm struggling for many hours.

So, I have Customers / Customer Categories (A,B,C) /Product Categories (X,Y,Z). My central table has the sales data (Customer No, Product No & Sales Amount) and there is also another table with customer data (Customer No & Customer Category) and another with products' data (Product No & Product Category), which are connected to the central one.

 I've calculated the shares of the product categories to every customer category :

-- Shares of Product Categories = DIVIDE([Total Sales],  CALCULATE([Total Sales],  ALL('Product Table')))

This works fine with customer Categories and the result of the Shares per Customer Category and product category is like this:

Customer Category	Product Categ. x	Product Categ. y	Product Categ. z
A	50%	30%	20%
B	40%	40%	20%
C	60%	10%	30%

It also works fine with single customers

Customer	Product Categ. x	Product Categ. y	Product Categ. z
Name1	30%	40%	30%
Name2	50%	30%	20%
Name3	20%	40%	30%

 So, let's say that Customer with Name 1 with shares 30/40/30 belongs to Customer Category B with product shares 40/40/20 as above.

The hard to do is to find the variance of every customer's product category shares to the shares of the products for his customer category.

It means that finally, I want to calculate 30-40 / 40-40 / 30-20 which will give -10/0/10.

Is it possible to create this measure and use it at the customer level?

Thanx

[Jihwan_Kim · Accepted solution]

Hi, @kostask 

Please correct me if I wrongly understood your question.

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

I created the sample and tried to apply your logic to my sample.

All measures are in the sample pbix file, and I hope you can easily follow the steps.

https://www.dropbox.com/s/qvigj4ihu246qcn/kostask.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: https://www.linkedin.com/in/jihwankim1975/

[kostask]

@Jihwan_Kim Couldn't be better than your solution. Thanx, a lot Kim for your precise reply and your time. Great work!!!