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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
beno
Frequent Visitor

Max for each subset of records

 

Hi

 

How could I obtain the max value for each product group in the table? This is what I have imaged to have:

 

New measure: Maxno = CALCULATE(MAX(pri[pri_no]);allselected(pri[ProductGroup]))

 

This is a subset of the data:

 

Product-Group      Pri-No    

A                             1

A                             2

A                             3

B                             1

B                             2

C                             1

D                            1

D                            2

D                            3

D                            4

 

This is the final wanted result:

A                            3

B                            2

C                            1

D                            4

 

 

Thanks for your comments.

Best Regards,

 

 

 

1 ACCEPTED SOLUTION
technolog
Super User
Super User

To achieve the desired result where you want to get the maximum value for each product group, you can use a combination of DAX functions. However, the measure you provided:

Maxno = CALCULATE(MAX(pri[pri_no]); allselected(pri[ProductGroup]))
will always give the maximum pri_no across all the selected product groups, rather than for each individual group.

To get the maximum value for each product group, you can simply use a calculated column, because a measure will always aggregate based on the context provided in a visual. In this scenario, you want to create a column that contains the maximum for each row based on the group it's in.

Here's how you can do it:

Using Calculated Columns:
Maxno = CALCULATE(MAX(pri[pri_no]), FILTER(pri, pri[Product-Group] = EARLIER(pri[Product-Group])))
This formula will create a new column in your table that, for each row, calculates the maximum pri_no value for that product group.

When you create a visualization, you would simply use Product-Group as your axis and then use the Maxno column as the value. Since all the values in a group are the same, it will show the correct max for each group.
If you decide later that you only want a table with unique product groups and their maximum pri_no, you can remove duplicates from the table visualization in Power BI or use the SUMMARIZE function to create a new table:

SummaryTable = SUMMARIZE(pri, pri[Product-Group], "MaxNo", MAX(pri[pri_no]))
This will give you a table with one row for each Product-Group and the corresponding maximum pri_no.

View solution in original post

3 REPLIES 3
technolog
Super User
Super User

To achieve the desired result where you want to get the maximum value for each product group, you can use a combination of DAX functions. However, the measure you provided:

Maxno = CALCULATE(MAX(pri[pri_no]); allselected(pri[ProductGroup]))
will always give the maximum pri_no across all the selected product groups, rather than for each individual group.

To get the maximum value for each product group, you can simply use a calculated column, because a measure will always aggregate based on the context provided in a visual. In this scenario, you want to create a column that contains the maximum for each row based on the group it's in.

Here's how you can do it:

Using Calculated Columns:
Maxno = CALCULATE(MAX(pri[pri_no]), FILTER(pri, pri[Product-Group] = EARLIER(pri[Product-Group])))
This formula will create a new column in your table that, for each row, calculates the maximum pri_no value for that product group.

When you create a visualization, you would simply use Product-Group as your axis and then use the Maxno column as the value. Since all the values in a group are the same, it will show the correct max for each group.
If you decide later that you only want a table with unique product groups and their maximum pri_no, you can remove duplicates from the table visualization in Power BI or use the SUMMARIZE function to create a new table:

SummaryTable = SUMMARIZE(pri, pri[Product-Group], "MaxNo", MAX(pri[pri_no]))
This will give you a table with one row for each Product-Group and the corresponding maximum pri_no.

kaushikd
Resolver II
Resolver II

MAX() Function is used to get the maximum possible value of any field, 

Now just you have to do is Create a measure

MaxNo = MAX(Pri[Pri-No])

 

Now In the Report view drop a table and into value select the field:-

Product-Group

MaxNo

 

The table will show the maximum Pri-No for each Product-GroupCapture.PNG

 

 

Based on the example, how to take it to another level of MAX.

 

This is there result from the MaxNo = MAX(Pri[Pri-No])

A                            3

B                            2

C                            1

D                            4

 

How to get the MAX from this group?

 

Desire output:

Group              MaxOfPri     Max OfGroup

A                            3               4

B                            2               4

C                            1                4

D                            4               4

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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