cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
silver3895 Regular Visitor
Regular Visitor

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

Accepted Solutions
silver3895 Regular Visitor
Regular Visitor

Re: Show positive and negative in TOPN filter

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.

6 REPLIES 6
TimHellegers Regular Visitor
Regular Visitor

Re: Show positive and negative in TOPN filter

Hi @silver3895 ,

 

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

 

Screenshot_1.png

TimHellegers Regular Visitor
Regular Visitor

Re: Show positive and negative in TOPN filter

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.

silver3895 Regular Visitor
Regular Visitor

Re: Show positive and negative in TOPN filter

Hi @TimHellegers,

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

Highlighted
TimHellegers Regular Visitor
Regular Visitor

Re: Show positive and negative in TOPN filter

Did you use the measure in the table?

tex628 New Contributor
New Contributor

Re: Show positive and negative in TOPN filter

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.

silver3895 Regular Visitor
Regular Visitor

Re: Show positive and negative in TOPN filter

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 6 members 885 guests
Please welcome our newest community members: