Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
jamieee0810
Regular Visitor

Re-Evaluating Minimum Based on Selection

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!

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
Super User

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)

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.

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.