Dax Product Measure for a Specific Name

[Anonymous]

Hi Guys,

I have the following table:

Product	Category	Sales Values
A	Red	2
A	Red	2
B	Red	3
B	Red	3
B	Red	2
C	Blue	2
C	Blue	2
C	Blue	2
C	Blue	2

I want to create a measure/column/visualization of some sort that calculates the sum of the sales values for a specific product and divide it by the sum of all sales values for the category that this product is in. Also I want to add a card that shows the result when i am filtering for a specific product without messing up my other data.

For example I want to calculate the share of Product A in Category Red: (2+2)/(2+2+3+3)*100.

I also want to do this type of calculation for other Products. Do I have to do it manually or is there some kind of way to make a function in the visualization pane where i can choose whatever product i want and it calculates it for me?

I tried sometheing like Share = CALCULATE(Sum(('Table'[Sales Value])), FILTER('Table', 'Table'[Product]="Red"))

[Anonymous]

@Omega What if i add a slicer and want my data to filter as the selection of the slicer?

[Omega]

In that case, you need to use allselected() function in your formula which reads the selected value. 

[Anonymous]

How would it fit in the formula that you gave me?

[Omega]

You will change allexcept with allselected 

[Anonymous]

It doesn't work because in this case it divides by itself and I always get 100%.

[AnkitBI]

As I understand, you will be using a Slicer on Product. Give below a try and let me know.

Measure 15 = 

var Category = CALCULATE(sum(Table1[Sales Values]),filter(all(Table1),Table1[Category] in VALUES(Table1[Category])))
return
DIVIDE(sum(Table1[Sales Values]),Category,0) * 100

[Anonymous]

@AnkitBI It doesn't seem to work in my case, it returns 0.

[AnkitBI · Accepted solution]

@Anonymous I have created a small PBIX file using your sample data. It is giving me correct values. Maybe if you can share the complete data or Model, I can give a further try. PBIX File

[Omega]

Try creating the below measure: 

Measure = 
var Category = CALCULATE(SUM(Table1[Sales Values]),ALLEXCEPT(Table1,Table1[Category]))
return
DIVIDE(SUM(Table1[Sales Values]),Category,0)*100

[Anonymous]

@Omega When I use it it returns a very close result but lower than expected in every case. Since the file is too large I can't manually where the error comes from. Maybe it doesn't take all the data I want, or it just divides by a larger amount. How can I make sure that even if there are duplicated products I still get the correct result?

[Omega]

Maybe it's due to rounding or hidden decimals? Double check the dataset 😉

[Anonymous]

@Omega It seems good but Category is a calculated column and doesn't work.

[LivioLanzo]

Hi @Anonymous,

you've a many-to-many relationship between products and categories, is that really the case?  Can one product really belong to different categories?

[Anonymous]

@LivioLanzo No, 1 product belongs to 1 category