Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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,
Solved! Go to Solution.
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.
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.
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-Group
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
18 | |
15 |
User | Count |
---|---|
36 | |
19 | |
19 | |
17 | |
11 |