Dynamic Column Calculation and Sum Based on slicer selector

[wei]

HI, I tried so much and could not figure out, hope some one could help me!

I have a table with 4 columns:

Table: TableXYZ

id	user	gender	value
1	a	1	10
2	a	2	5
3	a	1	10
4	b	0	1
5	b	2	1
6	b	1	5
7	c	1	6

What I want is with selecting different gender，

I could get a new measure 'sumvalue'  to sum value for every user, I also could 'rank' user according their sumvalue.  for example, if I select gender '1', I get:

user	gender	sumvalue	rank
a	1	20	1
b	1	5	3
c	1	6	2

I hope I explained it sufficiently and there is someone who could help!! thanks a lot!

[Dog]

Hi,

assuming I'm understanding correctly it sounds like you just need to create two new measures. 

SumofValue:=SUM(value)

and

Rank:=RANKX(ALL(TableXYZ),[SumofValue],,DESC)

add table visual

add columns user and gender and measures SumofValue and Rank

then add a slicer onto page for gender.

does that help?

Dog

[wei]

Many thanks, Dog! I followed your suggestion and added measures and table visual, the result is as below. Seems SumofVaule is correct but Rank is not we wanted. More help needed!

[Eric_Zhang · Accepted solution]

---

@wei wrote:

Many thanks, Dog! I followed your suggestion and added measures and table visual, the result is as below. Seems SumofVaule is correct but Rank is not we wanted. More help needed!

---

@wei

This measure shall work.

Rank =
RANKX (
    ALLSELECTED ( TableXYZ ),
    CALCULATE (
        SUM ( TableXYZ[value] ),
        ALLEXCEPT ( TableXYZ, TableXYZ[user], TableXYZ[gender] )
    ),
    ,
    DESC,
    DENSE
)

[Dog]

Hi, 

Try

Rank:=RANKX(ALLSELECTED(TableXYZ),[SumofValue],,DESC)