topic Re: Measure for price per order in DAX Commands and Tips

Measure for price per order

Grofu — Thu, 23 Jun 2022 17:15:18 GMT

Hello,

Is there any measure that i can use to calculate the order total when it contains multiple products.

I know that i can use visuals which will do this automatically but i need also a measure to be able to filter orders based on ammounts.

What i need is a measure that will return the total of an order ( AAA= 30 and BBB = 200) so i can then be able to filter these orders based on a given value (more than 50, more than 100, between 10 and 100 etc for which i will use a slicer).

Order	Product	Price
AAA	nnn	10
AAA	mmm	20
BBB	xxx	100
BBB	yyy	100

Re: Measure for price per order

kitgo2 — Thu, 23 Jun 2022 19:12:16 GMT

this should work:

Measure

#Price = CALCULATE(sum(Sheet1[Price]),ALLEXCEPT(Sheet1,Sheet1[Order]))
Column - which you can use as a slicer
*Price = [#Price]

Re: Measure for price per order

Grofu — Fri, 24 Jun 2022 14:47:47 GMT

Thank you for your help, it worked as expected for given example.

However, looking to the data, there are more scenarios that i did not forsee when using the column for slicer (which for me is much more useful).

Is there a way to have a column to return me the Expected result? I do not have other fields to differentiate.

Order	Product	Price	Result	Expected result
AAA	mmm	10	40	40
AAA	mmm	10	40	(blank)
AAA	nnn	20	40	(blank)
BBB	xxx	100	200	200
BBB	yyy	100	200	(blank)

Re: Measure for price per order

kitgo2 — Sun, 26 Jun 2022 21:55:36 GMT

See if this will work for you:

*Display = IF(Sheet1[Product] = Calculate(

minx (sheet1,[Product]),

FILTER (

ALL ( Sheet1 ),

Sheet1[Order] = EARLIER ( Sheet1[Order] )

&& Sheet1[Price] = CALCULATE(min ( Sheet1[Price] ),ALLEXCEPT(Sheet1,Sheet1[Order]))

)

), Sheet1[*Price], Blank())