Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there! My first post here and I guess it's a more complicated problem. The following data is given (excluding the win column!):
I have serveral dealers that sell the same products (look for product key) at different product prices and I would like to know the best dealer for each Product and in the second step for each product Categorie under the condition that he sells a minimum amount of 10 prodcts in each trade. If there are two dealers who have an equally good/best price they both "win" for that product. In the end I would like to have a stacked bar chart, that looks something like this:
It shows how often the dealers have the best price in each product categorie. I figure I need to perform the following steps:
- filter out the rows that have a min. amount that is <10
- group the products by product key and find the best price for each product key group (groupby + minx function??)
- ad a win to each dealer who has the lowest price for a certain product key
- if there are two or more equally low prices several dealers "win"
- in the end i need to sum up the wins of each dealer grouped by product categorie
I couldn't figure out the right DAX code until now, since I am quite new to DAX. Hope you guys can give me some valuable tips.
Thank you!
Thilo
Solved! Go to Solution.
Hi @Thilo ,
The following is my sample you can have a try.
Min = CALCULATE(MIN(Table1[Product Price]),FILTER(Table1,Table1[Product Key] = EARLIER(Table1[Product Key]) && Table1[Product Categorie] = EARLIER(Table1[Product Categorie]) && Table1[Min.Amount] >=10))
Win = VAR a = CALCULATE(MIN(Table1[Min]),FILTER(Table1,Table1[Min.Amount] >=10)) return IF(MAX(Table1[Product Price]) = a, 1,0) Total = SUMX(Table1,[Win])
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Thilo ,
The following is my sample you can have a try.
Min = CALCULATE(MIN(Table1[Product Price]),FILTER(Table1,Table1[Product Key] = EARLIER(Table1[Product Key]) && Table1[Product Categorie] = EARLIER(Table1[Product Categorie]) && Table1[Min.Amount] >=10))
Win = VAR a = CALCULATE(MIN(Table1[Min]),FILTER(Table1,Table1[Min.Amount] >=10)) return IF(MAX(Table1[Product Price]) = a, 1,0) Total = SUMX(Table1,[Win])
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-xuding-msft
Thank you very much Xue! Your solution is working for the test date. Concerning the real data I still have some issues, but I am positive that I'll be able to resolve them.
Best wishes
Thilo
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |