Forum Discussion
How do I make visual level filter reflect other visuals in my report
Hi, I have set up a matrix showing suppliers with spend >$25k. I have put a visual level filter on this matrix with the >$25 parameter set. My problem is this does not reflect in the other visuals in my report (i.e card showing number of suppliers, pie showing spend across divisions etc). How do I reflect the spend parameter across all visuals?
E.g - Only supplier 1 and 2 will be visible in the matrix, and the other visuals would only reflect supplier 1 & 2
Supplier Name | Spend | # of Invoices |
Supplier 1 | $50,000 | 56 |
Supplier 2 | $26,000 | 102 |
Supplier 3 | $5,000 | 20 |
I am also having the same issue with another report where i want to show suppliers with less than 10 invoices per year.
Hoping you can help.
Thank you
Hi,
Measures cannot be dragged to the filter section, Therefore, I think the best way out would be to write measures
Total amount = SUM(Data[Gross Amount])Total amount with condition = if([Total amount]>50,[Total amount],BLANK())Number of invoices = COUNTA(Data[Invoice Number])Number of invoices with condition = if([Total amount]>50,[Number of invoices],BLANK())Drag the 2nd and 4th measures to your visuals.
6 Replies
- PattemManohar
Community Champion
Burnsy Will "Report Level Filters" help you in this case ?
PattemManohar I have tried using the report level filter, however my value column is at a line level and I am trying to show total value per supplier, then remove any supplier with <$25k.
The matrix groups value by supplier and I can apply my filter at the matrix level, but it doesn't reflect in the other visuals. I think I need a formula or calculated column, but this is not my strong point and I have been struggling to apply something that reflects what I am looking for. My core data looks like the following:
Supplier Gross Amount Invoice Number Supplier 1 20 12354 Supplier 2 10 12222 Supplier 1 50 123A Supplier 3 5 22351 Supplier 3 50 25 Supplier 2 10 22335 Supplier 1 500 565688 The matrix or table I use in power BI groups my core data as follows:
Supplier Sum of Gross Amount Count of Invoice Number Supplier 1 570 3 Supplier 2 20 2 Supplier 3 55 2 Grand Total 645 7 I would then want to see in my table and other related visuals, only suppliers with a total amount of, for example, >$50. So supplier 2 would drop out of this table and all other visuals I have set up. Hope that makes sense.
v-lili6-msftCommunity Support
hi, Burnsy
After my test, you could try these two ways:
1. Add a Sum of Gross Amount column for Supplier by this formula
filter Column = CALCULATE(SUM(Table1[Gross Amount]),ALLEXCEPT(Table1,Table1[Supplier]))
then set report level filter that >$50.
2. create a Sum of Gross Amount measure by this formula and set visual level filter for each visual you would like to be affected.
Measure = CALCULATE(SUM(Table1[Gross Amount]),ALLEXCEPT(Table1,Table1[Supplier]))
and set visual level filter for each visual.
Best Regards,
Lin
