Forum Discussion
Product Price Variance
Create a measure for min price :
Min Price =
CALCULATE(
MIN('YourTable'[Price]),
ALLEXCEPT('YourTable', 'YourTable'[Item#])
)2. same for max price:
Max Price =
CALCULATE(
MAX('YourTable'[Price]),
ALLEXCEPT('YourTable', 'YourTable'[Item#])
)3. Variance:
Price Variance = [Max Price] - [Min Price]
This measure will reflect the difference between the highest and lowest prices across all invoices for each item.
Create a measure for min price :
Min Price =
CALCULATE(
MIN('YourTable'[Price]),
ALLEXCEPT('YourTable', 'YourTable'[Item#])
)
2. same for max price:
Max Price =
CALCULATE(
MAX('YourTable'[Price]),
ALLEXCEPT('YourTable', 'YourTable'[Item#])
)
3. Variance:
Price Variance = [Max Price] - [Min Price]
This measure will reflect the difference between the highest and lowest prices across all invoices for each item.
Worked perfectly! Thanks. Is there an easy way to apply the current active filter to the result set. Ex. Limit max and min of an item to the active invoices shown on the page?
Thanks for the replies from Ashish_Mathur and Shravan133.
Hi nroeder ,
Please try the following DAX formula:
Max = CALCULATE(MAX('Table'[Price]),ALLSELECTED('Table'))Min = CALCULATE(MIN('Table'[Price]),ALLSELECTED('Table'))Result:
Best Regards,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.