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 am having trouble geting a single max value for a row from three columns in a matrix.
The columns are: Name, Date(Month), Price
The Dataset:
Name | Month | Price |
A | January | 75.000 |
A | January | 150.000 |
A | March | 250.000 |
B | January | 350.000 |
B | January | 175.000 |
B | March | 150.000 |
C | March | 75.000 |
C | March | 65.000 |
C | January | 125.000 |
A simple Max() Formula will result in:
Name | January | March | Total (Max) |
A | 150.000 | 250.000 | 250.000 |
B | 350.000 | 150.000 | 350.000 |
C | 125.000 | 75.000 | 125.000 |
Total (Max) | 350.000 | 250.000 |
What I am aiming for:
Name | January | March | Total (Max) |
A | 250.000 | 250.000 | |
B | 350.000 | 350.000 | |
C | |||
Total (Max) | 350.000 | 250.000 |
So, it should show a blank when the the Max value is not valid in this context. C does not have the max price in either January or March hence blank.
Any suggestions?
Solved! Go to Solution.
Hi @merlingsf ,
We can try to create a measure to meet your requirement:
Measure =
SUMX (
DISTINCT ( 'Table'[Month] ),
VAR result =
MAXX (
CALCULATETABLE ( DISTINCT ( 'Table'[Name] ), ALLSELECTED () ),
CALCULATE ( MAX ( 'Table'[Price] ) )
)
RETURN
IF ( CALCULATE ( MAX ( 'Table'[Price] ) ) = result, result, BLANK () )
)
By the way, PBIX file as attached.
Best regards,
Hi @merlingsf ,
We can try to create a measure to meet your requirement:
Measure =
SUMX (
DISTINCT ( 'Table'[Month] ),
VAR result =
MAXX (
CALCULATETABLE ( DISTINCT ( 'Table'[Name] ), ALLSELECTED () ),
CALCULATE ( MAX ( 'Table'[Price] ) )
)
RETURN
IF ( CALCULATE ( MAX ( 'Table'[Price] ) ) = result, result, BLANK () )
)
By the way, PBIX file as attached.
Best regards,
Hi @v-lid-msft,
Thank you so much for taking the time to look at this.
This seems to solve the problem.
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 |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |