Same Column Variance

[Anonymous]

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]

Thank you so much, greatly appreciated

[jdbuchanan71]

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]

Thank you so much. Only one thing is missing. 

I have rates for mutiple cities such as city 1, city 2, city 3, City 4. With above Current Price measure , its calculating all cities current rate. How do I measure it per city? 

I appreciate your help 

[jdbuchanan71 · Accepted solution]

@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"
)