Forum Discussion
Same Column Variance
Hello, I have Table "Vendor_Rate" where I want to find out product price difference against "Current Rate" for all the vendors : Vendor 1 Rate, Vendor 2 Rate , Vendor 3 Rate
Rate | Price | Variance
Current Rate 150
Vendor 1 Rate 200
Vendor 2 Rate 500
Vendor 3 Rate 70
Vendor 4 Rate 50
I am using measure : Rate Difference = SUM(Vendor_Rate[Price])-CALCULATE(SUM(Vendor_Rate[Price],Vendor_Rate[Rate]="Current Rates")
I am looking to find price Variance and Varince % against "Current Rate"
Thank you in Advance
IK
Anonymous
You just need to change the [Current Price] measure to hold onto the city filter.
Current Price = CALCULATE ( [Vendor Price], ALLEXCEPT( Vendor_Rate, Vendor_Rate[City] ), Vendor_Rate[Rate] = "Current Rate" )
4 Replies
Hello Anonymous
I broke it into a couple measures so we would have them to use other places if needed.
Vendor Price = SUM ( Vendor_Rate[Price] )
Current Price = CALCULATE ( [Vendor Price], ALL ( Vendor_Rate ), Vendor_Rate[Rate] = "Current Rate" )Variance = [Vendor Price] - [Current Price]
Variance % = DIVIDE ( [Variance], [Current Price] )
Anonymous
You just need to change the [Current Price] measure to hold onto the city filter.
Current Price = CALCULATE ( [Vendor Price], ALLEXCEPT( Vendor_Rate, Vendor_Rate[City] ), Vendor_Rate[Rate] = "Current Rate" )