topic Re: Calculation Error in DAX Commands and Tips

Calculation Error

CM_Mills — Fri, 16 Sep 2022 13:17:42 GMT

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

Re: Calculation Error

PabloDeheza — Fri, 16 Sep 2022 13:28:59 GMT

Hi there!

Have you tried only this:

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

Re: Calculation Error

CM_Mills — Fri, 16 Sep 2022 13:41:23 GMT

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.

Re: Calculation Error

tamerj1 — Fri, 16 Sep 2022 15:23:30 GMT

Hi CM_Mills

would you please advise how does your model look like?

Re: Calculation Error

PabloDeheza — Fri, 16 Sep 2022 15:23:40 GMT

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] )

Re: Calculation Error

CM_Mills — Thu, 27 Oct 2022 14:18:26 GMT

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?