cancel
Showing results for
Did you mean:
Responsive Resident

Need to filter a table and currently only have a measure in my table visual

In my sales table I created a percentage measure that can give me the percantage of sales for each category the code is below:

``````% =
VAR total_count =
CALCULATE ( SUM ( Sales[Units] ) , ALL ( Sales[Product Category] ), ALL ( Sales[Sold Date] ), ALL ( Sales[Delivered Date] ) )
RETURN
(DIVIDE(SUM(Sales[Units]),total_count)) * 100``````

Next step was to display all my columns in a table with the % field

e.g.

 product category units % latest sold_date latest delivered_date mobile phones 10 16.67 19/01/2022 01/02/2022 televisions 5 8.33 19/01/2022 02/02/2022 desktop computers 15 25 21/01/2022 01/02/2022 laptops 20 33.33 20/01/2022 03/03/2022 games consoles 10 16.67 22/01/2022 04/02/2022

Next I want to filter the above to show only those product categories where % > 10%. How would I be able to achieve this?

UPDATE: Have solved how to do the above you just use the measure in the filter pane, but have another requirement whereby need to have an others group for % under 10% so if someone can show me how to do this I have seen other examples and it looks like may need a calculated table. I know the above example only shows one category under 10% but the above is only dummy data.

1 ACCEPTED SOLUTION
Super User

Hi,

Not sure to understand 100% your needs, but here is my guess :

As you will get mutiple results you should create a table, or concatenate the results...

Solution 1 : very simple, once you have your % in your table use filter panel to get value above 0.1 ie 10%
Solution 2 : create a table (using button new table) and using this formula will give you expected results) :

`Table = VAR CA_AllFAM = CALCULATE([TotalUnits] , ALL(Articles[Family]) )VAR TableFamily = ADDCOLUMNS( SUMMARIZE( Products, Products[Family]),  "Part_FAM" , DIVIDE (  [TotalUnits] , CA_AllFAM ) )RETURNFILTER( TableFamily, [Part_FAM]>0.1 )`
Solution 3 : Use above solution to create a virtual table in a VAR, and do some actions to deal with multiple results (concatenate, min, max...).

Hope it helps
Super User

Hi,

Not sure to understand 100% your needs, but here is my guess :

As you will get mutiple results you should create a table, or concatenate the results...

Solution 1 : very simple, once you have your % in your table use filter panel to get value above 0.1 ie 10%
Solution 2 : create a table (using button new table) and using this formula will give you expected results) :

`Table = VAR CA_AllFAM = CALCULATE([TotalUnits] , ALL(Articles[Family]) )VAR TableFamily = ADDCOLUMNS( SUMMARIZE( Products, Products[Family]),  "Part_FAM" , DIVIDE (  [TotalUnits] , CA_AllFAM ) )RETURNFILTER( TableFamily, [Part_FAM]>0.1 )`
Solution 3 : Use above solution to create a virtual table in a VAR, and do some actions to deal with multiple results (concatenate, min, max...).

Hope it helps

Announcements

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors