Cascade Calculation

[mortarotti]

Hi everyone,

I've had some issues about te calculation below. Here is a sample example.

I have two tables, sales and price.

Price

Market	Limit Volume	Value1	Value2
Residencial	10	3,99	1,25
Residencial	25	4,99	1,15
Residencial		5,99	1,03
Industry	100	99,99	0,99
Industry	500	115,99	0,93
Industry	1000	139,99	0,87
Industry		149,99	0,85

Sales

Customer	Market	Volume
1	Residencial	15
2	Industry	600
3	Residencial	5
4	Industry	1100
5	Industry	200

The calculation works in cascade, as follows: The Value1 referring to the line where the customer fits, for example the customer 1, with a volume of 15 is in the 2nd line of the Residential Market, since it is greater than the limit of the 1st line that is 10 but smaller than the limit of the 2nd line that is 25. The second step is to cascade the calculation, ie the first 10 units of volume it pays considering Value2 of the 1st line. Excluding the 10 that are already calculated, the remaining 5 are paid considering Value2 of the second line. The sum of these parts divided by the volume is equal to the price paid. I leave the example below in numerical form to facilitate understanding.

Customer 1 - Residencial Market - Total Volume: 15

(4.99 + (15-10) * 1.15 + 10 * 1.25) / 15 = 1.55

Customer 2 - Industry Market - Total Volume: 600

(139.99 + (600-500) * 0.87 + (500-100) * 0.93 + 100 * 0.99) / 600 = 1.16

Customer 3 - Residencial Market - Total Volume: 5

(3.99 + 5 * 1.25) / 5 = 2.05

I have no ideia how to make this rule works in dax. Can anyone help me please?

Thanks in advance,

Luis

[Anonymous · Accepted solution]

1.  Add columns to Price table: Include all of the necessary information for the calculation (except the volume, which comes from the Sales table). Also, Add a key column like "Residential 10-25"

2. Add the same key column to the Sales table, so that it will match the new key column in the Price table.

3. Create a relationship between the two tables.

4. Create a Measure with SUMX: SUMX('Sales', <Formula>)   //Refer to attributes in the Price table like this: Related(Price[Attribute])

Calc = SUMX('Sales',
   DIVIDE(
     Related(Price[Upper Value1]
     + ([Volume] - Related(Price[Lower Limit Volume])) * Related(Price[Upper Value2])
     + Related([Lower Limit Volume]) * Related(Price[Lower Value2]),
     [Volume]
   )
) 

Cheers!

Nathan