Forum Discussion
Zain26
Advocate II
Advocate IISecond and Third Maximum Amount and Corresponding Text
Hello All, Bit inexperienced with PowerBI, I am trying to calculate the Max, 2ndMax and 3rdMax amount based on the following columns. I want the Max of Sum of Amount for each Mapping, then I in...
Hi Zain26
First define a MEASURE to RANK the Mapping Values..
RANK = RANKX ( ALLSELECTED ( Table1[Mapping] ), CALCULATE ( SUM ( Table1[Amount] ) ), , desc, DENSE )Now you can get the 1st , 2nd and 3rd mapping values as
FirstMappingValue = CALCULATE ( MAX ( Table1[Mapping] ), FILTER ( ALLSELECTED ( Table1[Mapping] ), [RANK] = 1 ) )SecondMappingValue = CALCULATE ( MAX ( Table1[Mapping] ), FILTER ( ALLSELECTED ( Table1[Mapping] ), [RANK] = 2 ) )
Zubair_Muhammad
Community Champion
Community ChampionHi Zain26
First define a MEASURE to RANK the Mapping Values..
RANK =
RANKX (
ALLSELECTED ( Table1[Mapping] ),
CALCULATE ( SUM ( Table1[Amount] ) ),
,
desc,
DENSE
)Now you can get the 1st , 2nd and 3rd mapping values as
FirstMappingValue =
CALCULATE (
MAX ( Table1[Mapping] ),
FILTER ( ALLSELECTED ( Table1[Mapping] ), [RANK] = 1 )
)SecondMappingValue =
CALCULATE (
MAX ( Table1[Mapping] ),
FILTER ( ALLSELECTED ( Table1[Mapping] ), [RANK] = 2 )
)- Zubair_Muhammad
And you can get 2nd and 3rd Maximum Amounts using this pattern
File attached as well
Second Max Amount = VAR secondMapping = [SecondMappingValue] RETURN CALCULATE ( SUM ( Table1[Amount] ), Table1[Mapping] = secondMapping )