Incorrect Status based on Minimum value

[Anonymous]

Hi all,

Am stuck in a situation that I cant seem to solve, still learning so would appreciate if you could tell me what am I doing wrong and how do I get this right? It seems simple, but not for me anymore.

Background: ACME company sells products in various cities and i need to check if ACME is Cheaper, Same or Expensive in terms of pricing. The same product may be sold in 10 different stores in a city and we want to know:
1. How is ACME doing for each product by City or by Store and I calculate that as follows:

For each City:
a. List all the instances of that one product (lets say that product was sold in 10 stores in Dallas,so 10 instances,which means we may have 10 different store prices
b. Then retun the minimum price of these 10 prices
c. Compare this minimum price against the ACME price and determine whether the product is Cheap, Same or Expensive
d. Count the number of Cheap, Same and Expensive products

Formula:
1. Measure to calculate min price = 1 Min Price = CALCULATE(MINX(Data,Data[Store Price]),ALLEXCEPT(Data,Data[Barcode],Data[City],Data[Match Type],Data[Store]))

2. Calculated Column to detrmine status = 2 Price Status = if(Data[ACME Price]<[1 Min Price],"Cheap",if(Data[ACME Price]=[1 Min Price],"Equal","Expensive"))

Problem to be solved:
Screenshot 1 - When i have stores listed along with City, then the formula should consider the Store column and give me minimum price per Store (basically it will do a row by row comparison as a a store will show only once per city) which is what its doing in the "Min Price" and also the status is correct

Screenshot 2 - When I remove stores, the "Min Price" column is showing the right minimum price for a product per city, however the Status is incorrect - for product ending 7572, both the status must be Equal and for product ending 3566, it should be Expensive

Can someone please advise what am I doing wrong? How do i get the status right when viewing by City?

[Anonymous]

Can someone please assist?

[Anonymous]

bump

[HotChilli]

If you have a measure and a calculated column here (which I think you do), the column will be static from the moment it is created. The measure will change according to context.  I think that you want the Status to reflect changes in the measure (which it can't currently do).

There are two approaches: Make all features you require 'calculated columns' i.e. Min Price Per Store, Min Price Per City, Status v. Price Per Store, Status v. Price Per City.  You'll have to go this way if you want to put any of the Status fields in a slicer.

OR

Make the two fields that you created both measures.

[Anonymous]

Hi HotChilli,

You are right, the min value calculation is done using a measure and the Status is calculated using a calculated column. I need to be able to use "Status" as a slicer so I dont know if I can calculate Status as a measure. 

I tried using a calculated column to calculate min value and it just wouldnt show the right numbers.

Screenshot with stores with new calculations:

Screenshot without stores with new calculations:

I was hoping to get the results as below for

-without stores

with stores - 

Link to pbix file here:

https://drive.google.com/open?id=12E1eD1Gy88OUvmcalzO_e_mJ3rRKveGP 

Thank you so much for your help here HotChilli.

Please let me know your thoughts here.

[HotChilli]

I think you've solved the 'with stores' column formula.

The same formula won't work for 'without stores'.  Remember, you will need 2 columns if you're going down this route.

The other column would be

Chilli1.1 NEW - Min Price = CALCULATE(MIN(Data[Store Price]),ALLEXCEPT(Data,Data[Barcode],Data[City],Data[Match Type])) 

I think (though please test at your side)

[Anonymous]

Thank you so much HotChilli! I will give this a shot when i reach home and will keep you posted on how I go.

[v-chuncz-msft]

@Anonymous 

By the way, you may use ALLSELECTED instead of ALLEXCEPT if necessary.