cancel
Showing results for
Did you mean:
Frequent Visitor

How to Divide multiple rows for with another row from the same Column

Hi

I'm trying to create a measure where the denominator will always be same but the numerator will change. Both the Numerator and Denominator are referencing the same column. Below is an example. I need to get to \$_Share column. Would like to know how to get to that using DAX

 Customer Time Perriod Product \$ \$_Share ABC 1/25/2017 prod 1 100 1 100/100 ABC 1/25/2017 prod 2 75 0.75 75/100 ABC 1/25/2017 prod 3 55 0.55 55/100 ABC 1/25/2017 prod 4 60 0.6 60/100 DEF 12/22/2017 prod 1 125 1 125/125 DEF 12/22/2017 prod 2 100 0.8 100/125 DEF 12/22/2017 prod 3 75 0.6 75/125 DEF 12/22/2017 prod 4 60 0.48 60/125

Any Help would greatly be appreciated

1 ACCEPTED SOLUTION
Community Champion

Seems your logic is what @parry2k has stated.

As a calculated column, try this

```Column =
DIVIDE (
TableName[\$],
CALCULATE (
SUM ( TableName[\$] ),
FILTER (
ALLEXCEPT ( TableName, TableName[Customer] ),
TableName[Product] = "prod 1"
)
)
)```

As a MEASURE

```MEASURE =
DIVIDE (
SUM ( TableName[\$] ),
CALCULATE (
SUM ( TableName[\$] ),
FILTER (
ALLEXCEPT ( TableName, TableName[Customer] ),
TableName[Product] = "prod 1"
)
)
)```
Regards
Zubair

3 REPLIES 3
Super User III

what is logic which decides the value denominator? Seems like it is \$ value of 1st row of each customer where product = "Prod1" or something else?

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Community Champion

Seems your logic is what @parry2k has stated.

As a calculated column, try this

```Column =
DIVIDE (
TableName[\$],
CALCULATE (
SUM ( TableName[\$] ),
FILTER (
ALLEXCEPT ( TableName, TableName[Customer] ),
TableName[Product] = "prod 1"
)
)
)```

As a MEASURE

```MEASURE =
DIVIDE (
SUM ( TableName[\$] ),
CALCULATE (
SUM ( TableName[\$] ),
FILTER (
ALLEXCEPT ( TableName, TableName[Customer] ),
TableName[Product] = "prod 1"
)
)
)```
Regards
Zubair

Frequent Visitor

This is great It worked wonders  thanks

Announcements