topic Re: Measure query in DAX Commands and Tips

Measure query

gbarr12345 — Wed, 26 Jun 2024 02:01:49 GMT

Hi,

I'm trying to write a measure that calculates the margin.

I wrote the code and am getting the following error:

A single value for column 'amount' in table 'Module Sales with Inventory' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I have attached the measure below. Any help would be greatly appreciated.

Margin =

DIVIDE(

'Module Sales with Inventory'[amount] - 'Module Sales with Inventory'[cost amount],

'Module Sales with Inventory'[amount]

) * 100

Re: Measure query

Jihwan_Kim — Wed, 26 Jun 2024 03:07:44 GMT

Hi,

Please try something like below.

Margin = DIVIDE ( SUMX ( 'Module Sales with Inventory', 'Module Sales with Inventory'[amount] - 'Module Sales with Inventory'[cost amount] ), SUMX ( 'Module Sales with Inventory', 'Module Sales with Inventory'[amount] ) ) * 100

Re: Measure query

gbarr12345 — Thu, 27 Jun 2024 01:10:53 GMT

That seems to have worked!

Thank you so much!