Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
chiriazu
Frequent Visitor

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

Hi,

 

I have a below table, 

IDLevelSale
AALevel1 200
BBLevel2 300
CCLevel3 400
DDLevel1 250
EELevel4 500
FFLevel3 450
GGLevel2 350
HHLevel1 270

 

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

IDLevelSale
BBLevel2 300
GGLevel2 350

 

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

IDLevelSale
HHLevel1 270
AALevel1 200
DDLevel1 250

 

Please let me know how to achive this?

2 ACCEPTED SOLUTIONS
vicky_
Super User
Super User

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. 

View solution in original post

Anonymous
Not applicable

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

vxinruzhumsft_0-1672909512167.png

Output:

vxinruzhumsft_1-1672909526053.png

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.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

vxinruzhumsft_0-1672909512167.png

Output:

vxinruzhumsft_1-1672909526053.png

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.

@Anonymous  Thanks much, this also helps. 

FreemanZ
Super User
Super User

hi @chiriazu 

what if you select AA?

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

 

IDLevel  Sale
HHLevel1  270
DDLevel1  250
AALevel1  200

 

vicky_
Super User
Super User

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. 

Thank Vicky,

 

I have rechecked this and now its working. Thanks

Thanks for quick response.

 

But it didn't work. 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.