Forum Discussion
Product Price Variance
I currently have a table with Item#, Invoice#, Price, and Qty.
I want to be able to find price variances of products over mulitple invoices.
Ex. Item# AB-101 cost $4 on Invoice#101
Item# AB-101 cost $4.30 on Invoice#204
Also i'm using Direct Query.
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.
6 Replies
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.
Hi,
Drag Product to the Table visual. Write these measures
Min price = min(Data[Price])
Max price = max(Data[Price])
Hope this helps.
