wrong rank in negative values

[giorgiokatr]

wrong rank in negative values

when i select sector and area hierarchy rank for negative values (category bikes) goes wrong

after branch 6 branch 2 should have rank 2

also rank should start from 4

see attached pbix

https://drive.google.com/open?id=0Bw-_YmJ3Q49gTnpKTnQzNktTZ1k

[Eric_Zhang]

---

@giorgiokatr wrote:

wrong rank in negative values

when i select sector and area hierarchy rank for negative values (category bikes) goes wrong

after branch 6 branch 2 should have rank 2

also rank should start from 4

see attached pbix

https://drive.google.com/open?id=0Bw-_YmJ3Q49gTnpKTnQzNktTZ1k

---

@giorgiokatr wrote:

wrong rank in negative values

when i select sector and area hierarchy rank for negative values (category bikes) goes wrong

after branch 6 branch 2 should have rank 2

also rank should start from 4

see attached pbix

https://drive.google.com/open?id=0Bw-_YmJ3Q49gTnpKTnQzNktTZ1k

@giorgiokatr

Even with the attached pbix file, your description is not clear for me. Could you show the expected output with some snapshots?

[giorgiokatr]

please check two attached picsthank @Eric_Zhang

[Bordalos]

In order to better understand you issue please share how are you claculating the rank. 

[giorgiokatr]

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

[Bordalos]

@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)
					   )

[giorgiokatr]

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

[Bordalos · Accepted solution]

@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.

[giorgiokatr]

thanks @Bordalos!! that did the trick!

[giorgiokatr]

Hello @Eric_Zhang

Any ideas?