Forum Discussion
wrong rank in negative values
giorgiokatr what about :
First create a measure to sum [Total USD], like :
Sum_USD = SUM(temp[Total USD])
Then rank all table and use the last measure as expression:
Rank = RANKX(ALLSELECTED(temp),[Sum_USD],,ASC,Dense)
No filters applied
Filter
cat = {a,b}
filter
firstCat={1} and
cat={b,c}
In this way you will rank the values according to what is selected.
In order to better understand you issue please share how are you claculating the rank.
i want to calculate sales so i created a measure
salesamount = CALCULATE(SUM(Table1[sales]))
then i used
Rank = RANKX(ALL(Table1[branch]),CALCULATE([salesamount],ALLEXCEPT(Table1,Table1[branch],Table1[area],Table1[sector])),,ASC,Dense)
the "hierarchy" in matrix rows is sector>area>branch
but rank is wrong for negative when i filter for example sector or area in slicers
i also used
RANKX(ALL(Table1[branch]),CALCULATE([salesamount]),,ASC,Dense)
but same problem
rank goes 1,2 then in negative values rank is 4 instead of 3
this happen when i filter with a slicer
all branch rank (unfiltered area and sector ) is right
giorgiokatr you might need to nest the formula with hasonevalue( ).
See this really nice post from Anonymous http://community.powerbi.com/t5/Desktop/Using-RankX-in-DAX-For-PowerPivot-PowerBI/m-p/42824
Take this example:
Rank by visible = if ( HASONEVALUE(temp[cat]), RANKX(ALLSELECTED(temp[cat]),[Total USD],,ASC,Dense) )thanks Bordalos it works ok but the problem is when you have an upper hierarchy in the cat and you filter it.
example
try this in a power bi
and create two slicers one for first cat and a second for cat
if you filter by first cat (example first cat =1)
u will see wrong rank for
RANKX(ALLSELECTED(temp[cat]),[Total USD],,ASC,Dense)
first cat cat Total USD 1 a 50 1 b 20 1 c -10 2 d -20 giorgiokatr what about :
First create a measure to sum [Total USD], like :
Sum_USD = SUM(temp[Total USD])
Then rank all table and use the last measure as expression:
Rank = RANKX(ALLSELECTED(temp),[Sum_USD],,ASC,Dense)
No filters applied
Filter
cat = {a,b}
filter
firstCat={1} and
cat={b,c}
In this way you will rank the values according to what is selected.