Selected ID Sales compare with Other ID's on same Level

[chiriazu]

Hi,

I have a below table, 

ID	Level	Sale
AA	Level1	200
BB	Level2	300
CC	Level3	400
DD	Level1	250
EE	Level4	500
FF	Level3	450
GG	Level2	350
HH	Level1	270

In filter, when I select ID=BB, then the below result should show:

ID	Level	Sale
BB	Level2	300
GG	Level2	350

and again in filter, when I select ID=HH, then the below result should show

ID	Level	Sale
HH	Level1	270
AA	Level1	200
DD	Level1	250

Please let me know how to achive this?

[vicky_ · Accepted solution]

Here's how I would do it - firstly i would duplicate the ID and Level into a seperate table (you can do this in Power Query or DAX) and then add a slicer for Duplicate[ID]. I can then grab the related level using SELECTEDVALUE(Duplicate[Level]) and use that in a filter for the original table. So something like:

CALCULATE(SUM(OriginalTable[Sale]), OriginalTable[Level] = SELECTEDVALUE(Duplicate[Level]))

If you have multiple selections, then you can try and replace OriginalTable[Level] = filteredLevel with OriginalTable[Level] in VALUES(Duplicate[Leve]).

I haven't tested this code, so you'll need to do some tweaking to get it to work with your data. 

[Anonymous · Accepted solution]

Hi @chiriazu 

You can create a new table, and put the column of new table to the slicer

Table 2 = SUMMARIZE('Table',[ID])

Then create a measure in table

Measure = var _relatedlevel=MAXX(FILTER(ALL('Table'),[ID]=SELECTEDVALUE('Table 2'[ID])),[Level])
var _relateddata=MAX('Table'[Level])
return IF(ISFILTERED('Table 2'[ID])=FALSE(),1,IF(_relatedlevel=_relateddata,1,0))

Then put the measure in visual filter

Output:

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chiriazu]

@Anonymous  Thanks much, this also helps. 

[FreemanZ]

hi @chiriazu 

what if you select AA?

[chiriazu]

If we select ID = AA then the below result should show:

ID	Level	Sale
HH	Level1	270
DD	Level1	250
AA	Level1	200

[vicky_ · Accepted solution]

Here's how I would do it - firstly i would duplicate the ID and Level into a seperate table (you can do this in Power Query or DAX) and then add a slicer for Duplicate[ID]. I can then grab the related level using SELECTEDVALUE(Duplicate[Level]) and use that in a filter for the original table. So something like:

CALCULATE(SUM(OriginalTable[Sale]), OriginalTable[Level] = SELECTEDVALUE(Duplicate[Level]))

If you have multiple selections, then you can try and replace OriginalTable[Level] = filteredLevel with OriginalTable[Level] in VALUES(Duplicate[Leve]).

I haven't tested this code, so you'll need to do some tweaking to get it to work with your data. 

[chiriazu]

Thank Vicky,

I have rechecked this and now its working. Thanks

[chiriazu]

Thanks for quick response.

But it didn't work.