cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Thilo Frequent Visitor
Frequent Visitor

DAX - Complex problem (Nested Grouping + MInx ?)

Hi there! My first post here and I guess it's a more complicated problem. The following data is given (excluding the win column!):

grafik.png

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:

grafik.png

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX - Complex problem (Nested Grouping + MInx ?)

Hi @Thilo ,

The following is my sample you can have a try.

  1. Create a calculated column
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))

1.png

  1. Create measures
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])

21.png22.jpg23.jpg

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.

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: DAX - Complex problem (Nested Grouping + MInx ?)

Hi @Thilo ,

The following is my sample you can have a try.

  1. Create a calculated column
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))

1.png

  1. Create measures
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])

21.png22.jpg23.jpg

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.

 

Thilo Frequent Visitor
Frequent Visitor

Re: DAX - Complex problem (Nested Grouping + MInx ?)

@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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 308 members 3,197 guests
Please welcome our newest community members: