Forum Discussion
Using one row as variable for calculations
- 6 years ago
So I think your Jan example has an error in it as the total delivery cost only adds up to 8 when I think it should be 10.
But otherwise if I understand your requirements correctly I think the following calc should work for you
Delivery Costs = VAR _intAmt = CALCULATE(sum('Table'[Value]), 'Table'[Client] = "Int") VAR _totalAmt = CALCULATE(SUM('Table'[Value]) , ALL('Table'[Client]) , 'Table'[Client] <> "Int") VAR _clientAmt = CALCULATE(SUM('Table'[Value]) , KEEPFILTERS('Table'[Client] <> "Int")) VAR _proportion = DIVIDE(_clientAmt , _totalAmt) return (_proportion * _intAmt)The only slightly advanced bit in the above is that I've used the KEEPFILTERS() function when calculating the clientAmt (which is the total excluding the internal costs). In this way when you use the measure when the client column is on the rows it will get correctly filtered for the current client.
So I think your Jan example has an error in it as the total delivery cost only adds up to 8 when I think it should be 10.
But otherwise if I understand your requirements correctly I think the following calc should work for you
Delivery Costs =
VAR _intAmt = CALCULATE(sum('Table'[Value]), 'Table'[Client] = "Int")
VAR _totalAmt = CALCULATE(SUM('Table'[Value]) , ALL('Table'[Client]) , 'Table'[Client] <> "Int")
VAR _clientAmt = CALCULATE(SUM('Table'[Value]) , KEEPFILTERS('Table'[Client] <> "Int"))
VAR _proportion = DIVIDE(_clientAmt , _totalAmt)
return (_proportion * _intAmt)
The only slightly advanced bit in the above is that I've used the KEEPFILTERS() function when calculating the clientAmt (which is the total excluding the internal costs). In this way when you use the measure when the client column is on the rows it will get correctly filtered for the current client.
- Anonymous6 years agoNot applicable
Absolutely Brilliant!
I literally plugged in with my field names and it worked no errors first time!