Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following table :
EAN | Store | Price | Date |
AAAAA | Amazon | 100 | 01/01/2022 |
AAAAA | Ebay | 120 | 01/01/2022 |
AAAAA | Amazon | 110 | 08/01/2022 |
AAAAA | Ebay | 130 | 08/01/2022 |
AAAAA | Ali | 105 | 08/01/2022 |
CCCCC | Amazon | 10 | 01/01/2022 |
CCCCC | Ebay | 12 | 01/01/2022 |
CCCCC | Amazon | 10 | 08/01/2022 |
CCCCC | Ali | 11 | 08/01/2022 |
EAN is the reference of a product selled by Amazon, Ali... I have the product price updated every week. I need to know, for a disctinct product, what is the store selling the product at the cheapest price.
For example, if the date filter is all January, the results should be:
EAN | Cheapest_store |
AAAAA | Ali |
CCCCC | Amazon |
As you can see, only the last updated date should be taken into account (if there is data).
I would like also to be able to count the number of time each store has been the cheapest one (based on last date as well):
Store | Count_cheapest |
Ali | 1 |
Amazon | 1 |
Ebay | 0 |
I tried the following dax formula but it does not give the expected results:
'''
var maxDate = CALCULATE(MAX(full_pricing_stores[date]))
var min_Price = [min_price_concurrents]
RETURN
CALCULATE (
COUNTROWS (full_pricing_stores),
FILTER( full_pricing_stores, full_pricing_stores[date] = maxDate && full_pricing_stores[price] = min_Price),
ALLEXCEPT(full_pricing_stores, full_pricing_stores[store] )
)
'''
Any helpd & advice would be greatly appreciated 🙂
Thank you
@thebaguette , Try a measure like
Rankx(Filter(allselected(Table[EAN], Table[Month Year], Table[Store]), [EAN] = Max([EAN]) && [Month Year] = max([Month Year])), calculate(Min(Table[Price])),,asc,dense)
you can use visual level filter of 1
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
Hi @amitchandak,
Thank you for your answer. Unfortunately it didn't work. I did some calculation before connecting the table to PBI to make it work 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |