Product Price Variance

[nroeder]

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.

[Ashish_Mathur]

Hi,

Drag Product to the Table visual.  Write these measures

Min price = min(Data[Price])

Max price = max(Data[Price])

Hope this helps.

[Shravan133 · Accepted solution]

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.

[nroeder]

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?

[v-linhuizh-msft]

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,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[nroeder]

That's close but how would you make it Item specific? So only show the variance per item not for all items displayed?

[v-linhuizh-msft]

Hi @nroeder ,

If you want to show the variance per item item, I think you just need to follow Shravan133's  answer and then filter use Invoice# field.

Best Regards,
Zhu