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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Show positive and negative in TOPN filter

Hi. Creating these tables with formulas in Excel is easy but I want to create this in Power BI. All 3 of these columns are aggregates. And I am filtering out the top 15s using the last column. So I need both positive and negative values. How do I do this in Power BI?

 

2.jpg

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thanks for your replies everyone.

I was having the table aggregate values for me and that's why I could only use half of your solutions. So I created another column of those same aggregates and added that pre-calculated column to my table. And then I was able to turn that column into absolute values, and use that to filter TOPN.

 

I am marking this as closed now.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

I might have misinterpreted your situation.

 

You could create a measure like below;

Measure = ABS(Values of last column)

 

Now you will only use the absolute values and thus negative and positive values. Use this measure to filter the top 15.

Use your regular values to show in the visual. Because the measure will show you the values in positive format.

Anonymous
Not applicable

Hi @Anonymous,

I am using aggregates to make my table. I tried the ABS solution but it wasn't working since the aggregates get messed up.

tex628
Community Champion
Community Champion

You need to TOPN filter a different calculation than your current comparison. You can make a calculation that converts the all diffs to positive. You dont have to display the calculation you use for filtering, so you can display your original and use the other for the filter.


Connect on LinkedIn
Anonymous
Not applicable

Thanks for your replies everyone.

I was having the table aggregate values for me and that's why I could only use half of your solutions. So I created another column of those same aggregates and added that pre-calculated column to my table. And then I was able to turn that column into absolute values, and use that to filter TOPN.

 

I am marking this as closed now.

Anonymous
Not applicable

Did you use the measure in the table?

Anonymous
Not applicable

Hi @Anonymous ,

 

You can use 'data bars' under conditional formatting in the table visual to achieve this.

 

Screenshot_1.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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