Forum Discussion

sagar92volt's avatar
sagar92volt
New Member
9 years ago

Two level grouping and ranking

I have following dataset:

Year Location buyers Amount
2015 West A 12
2015 West A 14
2015 East Z 55
2015 South Y 62
2015 West O 24
2015 East B 10
2015 East B 12
2015 North B 23
2015 South K 10
2015 North C 23
2015 North M 23

Final output

if we chose top=3
West A 14
East Z 55
East Z 22
South Y 62
South K 10
North B 23
North C 23
North M 23

if top=1
West A 14
East Z 55
South Y 62
North B 23

 

i have to prepare a clustered bar chart that shows top n buyers for each region in terms of sum of amount they each bought (separately for each region).

enter image description here

but in actual there should be no gaping like this 

 

enter image description here

 

I have created a new measure 

Measure 10 = CALCULATE (
[Measure for summation of amount],
KEEPFILTERS (
GENERATE (
VALUES ( Table1[region]),
TOPN ( 5, ALL ( Table1[buyer] ), [Measure for summation amount] )
)
)
)

 

1 Reply