topic DAX: Divide product by their category in DAX Commands and Tips

DAX: Divide product by their category

Anonymous — Tue, 12 May 2020 15:02:06 GMT

Hi everyone,

Thanks in advanced for your help!

I guess you could help me with this issue:

Table= Test

Category	Product	Value
A	Blue	10
A	Green	20
B	Yellow	30
C	Orange	15
C	Violete	5
D	Black	15

I am trying (without success) to do a measure in DAX as follows:

Measure= individual product (i.e) Blue) / Their category (A)
It will have to return-> 10/30

My current measures are:
Numerator= SUM(Test[Value])
Denominator=

CALCULATE(

SUM(Test[Value]), FILTER(ALLSELECTED(Test[Product])))

I have tried by ALL, ALLSELECTED, ALLEXCEPT...

Thanks,

Re: DAX: Divide product by their category

camargos88 — Tue, 12 May 2020 15:09:49 GMT

Anonymous ,

Re: DAX: Divide product by their category

nandukrishnavs — Tue, 12 May 2020 15:12:49 GMT

Anonymous

Try this

Measure = var _category=SELECTEDVALUE(TestTable[Category]) var _value= SELECTEDVALUE(TestTable[Value]) var _total= SUMX(FILTER(ALL(TestTable),TestTable[Category]=_category),[Value]) var _result=_value/_total return _result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Re: DAX: Divide product by their category

Anonymous — Tue, 12 May 2020 15:41:40 GMT

camargos88 Thanks, that works, nevertheless not in my case I simply to much my case. 😞 I'm sorry..

I have three tables:

Table 1:

id_1	category	Value
1	A	10
1	A	20
2	B	30

Table 2

id_2	id_1
10	1
11	1
13	2

Table 3:

id_2	Product
10	Blue
11	Green
13	Yellow

I have three tables connected, by id. My visual table should be:

Product	Category	Division
Blue	A	=10/30
Green	A	=20/30

Re: DAX: Divide product by their category

camargos88 — Tue, 12 May 2020 15:59:32 GMT

Anonymous ,

The problem here is the relationship, example:

Product Blue has id 10. On table 2 it's associated with id_1= 1. However on table1 id_1 has 2 rows.

Which one is for Product Blue ? It's impossible to know, that's why you code didn't work for the first time.

Take a chance to redesign your model.