Re-Evaluating Minimum Based on Selection

[jamieee0810]

Hi Guys,

I'm not new to Power BI, but this problem is making me feel like it, so I thought i'd reach out to people smarter than me to get some assistance! I've tried looking around the forums, but can't see anything posted which poses a similar problem.

I'm doing some price analysis on products which the company I work for, and some competitors sell, and have some data which is cut by Day/Item/Seller and has a Price for each row, not all competitors sell all products. It looks like this:

Date | Seller | Item | Price

01/01/2022 | Seller A | AB123 | 15

01/01/2022 | Seller B | AB123 | 10.99

01/01/2022 | Seller C | AB123 | 12

01/01/2022 | Seller A | AB124 | 25

01/01/2022 | Seller B | AB124 | 28

01/01/2022 | Seller A | AB125 | 35

01/01/2022 | Seller C | AB125 | 30

I want to present a matrix which counts the number of items which are sold, and counts where the price is equal to the minimum price, i.e. count the number of times the seller is showing the cheapest option; such as this:

Date | Seller | Item Count | Minimum Count

01/01/2022 | Seller A | 3 | 1

01/01/2022 | Seller B | 2 | 1

01/01/2022 | Seller C | 1 | 1

I got that working fine with a calculated column using CALCULATE(MIN('Table1'[Price]), ALLEXCEPT('Table1','Table1'[Date], 'Table1'[Item])) and then using that to create and indicator to show where the price = min price for the day/item and a measure which counts them.

The problem I'm having is that I need to be able to filter out certain sellers and have the minimum price recalcute based on the selection. i.e. if I filtered out Seller C, I would want the matrix to show as:

Date | Seller | Item Count | Minimum Count

01/01/2022 | Seller A | 3 | 2

01/01/2022 | Seller B | 2 | 1

It feels like it shouldn't be as difficult as I'm finding it, though - I've tried for around 3 solid days now, using various combinations of measures, calculated tables etc but just can't get it to work - I'm hoping someone will be able to give me a nudge in the right direction.

Ideally, I would also be able create a column which i could use to filter further based on % difference from the minimum price as well, but I'm not sure that is going to be possible? It doesn't feel like it will be based on the things I've tried so far.

Thanks, would really appreciate the help!

[Ashish_Mathur]

Hi,

Have you been able to solve this question?  Could you kinldy explain why the answer for Seller A should be 2 in Table 3 of your original message?

[jamieee0810]

I've not been able to apply the suggestion yet, but will be trying it later. In response to your question, it's because Supplier C was hypothetically filtered out (via a page level slicer) leaving Seller A as the only seller on the third item, making it the cheapest price.

[lbendlin]

ALLEXCEPT is a rather blunt tool.  You may want to use REMOVEFILTERS instead.

How are you "filtering out certain sellers"  ?  Page level filter? Disconnected slicer?

Are you assuming that each seller is only selling products once on each day?  What would happen if Seller A sold item AB123 once for 15 and once for 14 - on the same day ?

On a general note - not sure you are aware but your issue is anything but simple. It involves severe pointer gymnastics, going from a seller to the products they sold to the sellers who sold these products to the minimum to a comparison. No wonder this has you stumped for a while. 

Cheapest = 
var a = SUMMARIZE('Table',[Item],"mp",min('Table'[Price]))
var b = ADDCOLUMNS(a, "cmp",CALCULATE(min('Table'[Price]),REMOVEFILTERS('Table'[Seller])))
var c = filter(b,[cmp]=[mp])
return countrows(c)

[jamieee0810]

Thanks again for your response, to answer your questions:

1. I'm using a page level slicer to filter out the sellers
2. I am fixing duplication issues in the SQL which loads the data into Power BI, there will only ever be one record for each Day/Item/Seller - though for clarification, there will be multiple days where the data could be different to the previous day, but I added the date field into the SUMMARIZE part to account for that.

I tried your suggestion, and put the code into a calculated measure, but unfortunately it did not have the desired results; before filtering it matched perfectly to the numbers which I was getting, though upon filtering the numbers do not change so I'm not sure the MIN is getting recalculated. I suspect it has something to do with the REMOVEFILTERS but I changed it to ALLEXCEPT, amongst some other things, and got the same results.

If you have any suggestions they would be appreciated.

[jamieee0810]

Thanks so much for your reply, I needed a break from it so started something else which snowballed! I'll be trying out your suggestion later today, I'll let you know how I get on.