Calculation Error

[CM_Mills]

Hi,

I have a calculation that isn't working as expected when using two different data points.

Looking at the example below i want to be able to if i filter on just 'data 1' get the result 5. 

Then if i filter on 'data 1' and 'data 2' together it should result in 15,046. Where the calculation is done one at a time then added together.

However I’m currently getting the result of the 'Number' added together and the 'multiplier' added together and then TIMES each other resulting in 16,573. 

My query is:

Revenue - Total Including Predicted (USD) = (SUMX('Revenues','GST Revenues'[Predicted Revenue Calculator (USD)]*SUMX(Data,Data[Volume - Lots])))+SUMX('GST Revenues','GST Revenues'[Revenue - Total (USD)])

I also have a unique identifier for each data point if required: ‘Unique Identifier (Inc. Year & Month)

Result Wanted
	Number	Mutiplier	Result
Data 1	42	0.13	5
Data 2	11,339	1.33	15,041
			15,046
Currently Happening
	Number	Mutiplier	Result
Data 1	42	0.13
Data 2	11,339	1.33
	11,381	1.46	16,573

[tamerj1]

Hi @CM_Mills 

would you please advise how does your model look like?

[PabloDeheza]

Hi there!

Have you tried only this:

Revenue - Total Including Predicted (USD) =
SUMX(
    'Revenues',
    'GST Revenues'[Predicted Revenue Calculator (USD)] * Data[Volume - Lots]
)

[CM_Mills]

Hi, Thanks for reviewing. Without the sumx before the volume i get the following:

A single value for column 'volume - lots' in table 'data' cannot be determined. This canhappen when a measure refers to a column that contains many values without specifying an aggregation such as min,m ax,count or sum to get a single result.

[PabloDeheza · Accepted solution]

Not sure I follow correctly how the model is built, but for the tables is see in the images, this should work.

SUMX(
    SUMMARIZE(
        'YourTable',
        'YourTable'[Identifier],
        'YourTable'[Multiplier],
        'YourTable'[Number]
    ),
    'YourTable'[Number] * 'YourTable'[Multiplier]
)

[CM_Mills]

@PabloDeheza Thank you for taking a look.

I have tried your solution but it isn't working. I think the issue is that 'Number' and 'Multiplier' are located in different tables. How would i take this into account?