Forum Discussion
Solved
Max value from two columns - Show blanks
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?
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,
2 Replies
v-lid-msftCommunity Support
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.
