I have the following list which shows . If I delete Column Rates, the Amount (is a cost value multiplied by the Rates) column should aggregate up to say 600, but its give me Multiivalue.
I have tried the ForEach command and no luck.
Rates Amount
1.2 -
3 - 100
4 -
2.1 -
0.5 - 500
Any help is appreciated pls. Really stuck on this one.
Sims
If your formula is:
=[Rates] * [Amount]
Use:
=Sum([Rates] * [Amount])
Automatic aggregation doesn’t work when multiplying dimensions by measures. As such, your cell will be trying to display a table of 3 * 100 and 0.5 * 500 and only wants one value in the cell rather than multiple, hence the #MULTIVALUE error.
Thanks mate. Did try that but no luck.
OK, was hoping for an easy one. 
Let’s start from the beginning.
What types are each object?
For example, Rates - dimension, numeric?
Hi Mark,
Thanks for coming back, I have attached a screenshot maybe to help with the issue
When I delete Columns B & C I was expected Columns values to sum but I end up with Mutli Value issue
Again Many thanks
Zahed
I take it you’ve used ForEach ([Country]) to give a [Rate] for each row, which is why you’ve got blank rows where there are no amounts?
That makes Rate a dimension, a bit like price. While it’s a number by format, it’s actually a dimension.
What does just =Sum([Rate] * [Amount1]) give?
I’d hope it is 757,384.90 so that it is adding your two Final Amounts.
If it’s giving 1,335,197.78 then you’re adding the rates first then multiplying by the amounts.
Each Amount1 looks like it has its own rate, so you shouldn’t need to worry about [Rate] getting confused between them.
