Forum Discussion

DSmith4's avatar
DSmith4
Icon for Helper II rankHelper II
6 years ago
Solved

How to see underlying data from a matrix in a table

Hello,

 

I have a matrix that shows me the sum of the vendor balance for the criteria below:

Days aged >=60

ABS value of the sum of items over 60 days >= 100,000 

 

I have a table below it with the transactions that make up this balance but it shows all data, not only the vendors with a balace of 100,000 for items over 60 days.

 

How can I filter the "all data" table that I have to only show items in that are composed of the matrix?

 

Thanks

  • Hi DSmith4 ,

     

    You need a measure and then put the measure in the value field of a matrix:

     

    Measure = SUM('Table'[Value])

     

    Then put the measure in the filter field and you will see:

     

     

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly

3 Replies

  • kentyler's avatar
    kentyler
    Icon for Solution Sage rankSolution Sage

    In power bi you can have filters that apply to a single visual, filters that apply to the whole page, and filters that apply to all the pages in a report.

    As you can see I have applied a filter to show year = 2 in the filter pane where it says "filters on this page" and both the tabular visual and the table visual are respecting the filter 

    I'm a personal Power Bi Trainer I learn something every time I answer a question. I blog at http://powerbithehardparts.com/

    The Golden Rules for Power BI

    1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
    2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
    3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
    4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.
  • You may need to group data at the vendor level and filter. Check how data filtered at invoice level

     

    Total value of claims over 20,000 USD= Calculate(
        sum(Invoices[Amount]);
        FILTER(  
            ADDCOLUMNS( 
                SUMMARIZE(
                    Invoices;
                    Invoices[Invoice ID]
                );
                "Over20K";
                calculate(
                    sum(Invoices[Amount]);
                    filter(
                        ALL(Invoices);
                        Invoices[Invoice ID]= earlier(invoices[Invoice ID])
                    )
                )
            )
            ;[Over20K]>20000
        )
    )

     

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support

    Hi DSmith4 ,

     

    You need a measure and then put the measure in the value field of a matrix:

     

    Measure = SUM('Table'[Value])

     

    Then put the measure in the filter field and you will see:

     

     

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly