cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
schwinnen Member
Member

Filtering on totals - Major Power BI Flaw???

I have tried multiple times to get this resolved with no success, so I am going to try one more time.  It is something so basic and, for me, so necessary that I cannot believe it is not only possible, but easy.  Below is a link to a file showing a very simplified version of what I need to accomplish.

 

https://1drv.ms/f/s!Am--E_G2VxhMg9gKVT1RZibIMhimAQ

 

All I would like to do with this file is create a slicer that will allow me to filter on aggregated data, such as showing only customers over X number of orders or only customers over X dollars in sales.  I need this slicer to work with other slicers and I need it to work with filters on my reports.  

 

To me, this is such a basic function that I cannot understand why it is so difficult.  I would probably use it for every single report I do.  I have spent entire days trying to figure this out

 

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHaas Established Member
Established Member

Re: Filtering on totals - Major Power BI Flaw???

Weird, can't upload a file there.

 

Oh well, use this link.  Public folder.

 

OneDrive Folder

 

Anyway, search for "Power BI Paramters", you'll find a slew of information.

 

Also, Rob Collie used to call this trick a "harvesting measure".  You'll see mention of the same thing on his blog at www.powerpivotpro.com

 

Cheers,

 

~ Chris

View solution in original post

9 REPLIES 9
Highlighted
ChrisHaas Established Member
Established Member

Re: Filtering on totals - Major Power BI Flaw???

Definitely not impossible, just need to use Parameters in Power BI and set filters based on those parameters.

 

I've built a quick solution that does this, but I don't have write access to that onedrive account.  Can you make that folder public (anyone can edit)?

 

 

schwinnen Member
Member

Re: Filtering on totals - Major Power BI Flaw???

https://1drv.ms/f/s!AG--E_G2VxhMbw

 

I had to move them to another folder to make it public.  Hopefully I did it right.  I've never made a onedrive folder public before.

ChrisHaas Established Member
Established Member

Re: Filtering on totals - Major Power BI Flaw???

Weird, can't upload a file there.

 

Oh well, use this link.  Public folder.

 

OneDrive Folder

 

Anyway, search for "Power BI Paramters", you'll find a slew of information.

 

Also, Rob Collie used to call this trick a "harvesting measure".  You'll see mention of the same thing on his blog at www.powerpivotpro.com

 

Cheers,

 

~ Chris

View solution in original post

schwinnen Member
Member

Re: Filtering on totals - Major Power BI Flaw???

I probably did something wrong when sharing that folder.  Anyway, parameters seems like a great solution.  I use a parameter for standard deviations for another report so I should have thought of that.  Anyway, just switched over to Power BI from Tableau.  I like Power BI much better actually, but getting used to the differences and getting out of the mindset of how I used to do it is taking some time.  Great help, once again though!

schwinnen Member
Member

Re: Filtering on totals - Major Power BI Flaw???

This is brilliant, by the way.  Really saved the day.  I have posted 3 messages about this in the community and spent days trying to figure it out.  If I could buy you a virtual beer somehow, I would gladly do it.

schwinnen Member
Member

Re: Filtering on totals - Major Power BI Flaw???

@ChrisHaas, do you know if I am able to use parameters to filter on a bar chart?  I am working with the parameter as you showed me and it works great to filter a table, but I'm not able to get it to filter the bar chart.  I can work with it either way, but filtering the bar chart with the parameter would be ideal.

ChrisHaas Established Member
Established Member

Re: Filtering on totals - Major Power BI Flaw???

@schwinnen, should be able to with no issue.  All Power BI visuals are essentially tables of information, with HTML/CSS applied to make them look like a graph.  

 

Are you sure you're using the new measures that "listen" to the parameter that you select via the slicer?

schwinnen Member
Member

Re: Filtering on totals - Major Power BI Flaw???

I believe I am but I will work on it more in a little while.  The interesting thing was that I added that new measure to the tooltip on the bar graph.  When I used the parameter, it did not filter out the data outside of the parameter, but any bar that did not fit inside the parameter no longer displayed that measure on the tooltip.  If it should work, I will figure out what I am doing wrong.

schwinnen Member
Member

Re: Filtering on totals - Major Power BI Flaw???

I figured out what is going on.  I am using the parameter you showed me to display locations with a minimum number of LOADS.  However, I am not using LOADS as a value in my graph.  My graph is average time at a location and I want to filter to only show locations with a certain number of records or LOADS.  Even if I add LOADS as a value to my chart so that I have two bars (one for average time at location and one for LOADS), then use the parameter, all of the locations will still be displayed, but the bar for number of LOADS will disappear for those locations under the parameter value.  

 

This is not a huge deal.  I came up with a workaround where I created a table of locations and number of LOADS.  I then use the parameter to filter this table.  The user can then just click on the location in the table to filter the graph.  This is not quite as efficient as having the parameter control filter the graph, but it works.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 317 members 2,959 guests
Please welcome our newest community members: