Forum Discussion
RANKX depending on filter context
- 4 years ago
Here's what I ended up doing. amitchandak TOPN was the soultion after all, thanks.
SalesVolume:=var Top5 =
TOPN(5,SUMMARIZE(
ALLSELECTED(Producers),
Producers[HoldingCo],
"TopSalesAmt",
[SalesAmt]
),[TopSalesAmt])
var Subtotals = NOT(HASONEVALUE(Producers[HoldingCo])
var TopRank = IF(HASONEVALUE('Top'[Group]) && VALUES('Top'[Group])="Top 5",1,0)
var Top5Sum= SUMX(Top5,[SalesAmt])
var Top5Total = IF(NOT(HASONEVALUE(Producers[HoldingCo])) && HASONEVALUE('Top'[Group]) && TopRank = 1,TRUE(),FALSE())
var OtherTotal = IF(NOT(HASONEVALUE(Producers[HoldingCo])) && HASONEVALUE('Top'[Group]) && TopRank = 0,TRUE(),FALSE())
return
IF(NOT(HASONEVALUE('Top'[Group])),[SalesAmt],
IF(Subtotals=TRUE(),
IF(TopRank=0,CALCULATE([SalesAmt],ALL('Top'[Group]))-Top5Sum,Top5Sum),
IF(Subtotals=FALSE(),
IF(TopRank=0,IF(NOT(CONTAINS(Top5,Producers[HoldingCo],VALUES(Producers[HoldingCo]))),[SalesAmt],BLANK()),
IF(CONTAINS(Top5,Producers[HoldingCo],VALUES(Producers[HoldingCo])),[SalesAmt],BLANK())))))
Here's what I ended up doing. amitchandak TOPN was the soultion after all, thanks.
SalesVolume:=var Top5 =
TOPN(5,SUMMARIZE(
ALLSELECTED(Producers),
Producers[HoldingCo],
"TopSalesAmt",
[SalesAmt]
),[TopSalesAmt])
var Subtotals = NOT(HASONEVALUE(Producers[HoldingCo])
var TopRank = IF(HASONEVALUE('Top'[Group]) && VALUES('Top'[Group])="Top 5",1,0)
var Top5Sum= SUMX(Top5,[SalesAmt])
var Top5Total = IF(NOT(HASONEVALUE(Producers[HoldingCo])) && HASONEVALUE('Top'[Group]) && TopRank = 1,TRUE(),FALSE())
var OtherTotal = IF(NOT(HASONEVALUE(Producers[HoldingCo])) && HASONEVALUE('Top'[Group]) && TopRank = 0,TRUE(),FALSE())
return
IF(NOT(HASONEVALUE('Top'[Group])),[SalesAmt],
IF(Subtotals=TRUE(),
IF(TopRank=0,CALCULATE([SalesAmt],ALL('Top'[Group]))-Top5Sum,Top5Sum),
IF(Subtotals=FALSE(),
IF(TopRank=0,IF(NOT(CONTAINS(Top5,Producers[HoldingCo],VALUES(Producers[HoldingCo]))),[SalesAmt],BLANK()),
IF(CONTAINS(Top5,Producers[HoldingCo],VALUES(Producers[HoldingCo])),[SalesAmt],BLANK())))))