In power bi how do I filter a field by the sum of another field?

[Ninodekip]

Hi everyone,

I have been dealing with a power bi question for the last couple of days and haven't been able to find a working solution. It feels like an issue that is easy to fix, but I'm not exactly sure how.

I have a table in my semantic model with Salesforce data. The table has 3 columns that are relevant for this question.

I have a column [accountid] and each account id can have multiple rows with an [opportunityid], that each have an amount of money [Totale koopsom eigen geld] in the corresponding field of the same row.

I want to display a matrix visual that has account and opportunity id's as the rows with the amounts of money in the values field. 
Here comes the difficult part: I only want to display account id's where the sum of the corresponding rows in [Totale koopsom eigen geld] is over 100.000.
I have tried filtering the data in many different ways, but each time power BI only returns the individual fields of [Totale koopsom eigen geld] that are over 100.000. (While I want to see account Id's that for instance have 3 rows in [Totale koopsom eigen geld] that sum to a total of 100.000 or more)

The result should be similar in form to Top N advanced filtering the account id by the value of the sum of the money field (shown below). Only instead of Top N it should be all account id's where the sum is over or equal to 100.000 as seen here 

Thank you in advance!

[Anonymous · Accepted solution]

Hi @Ninodekip 

You can refer to the following solution.

Sample data 

Create a measure

MEASURE =
VAR a =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [Accountid],
        "Sum", SUM ( 'Table'[Value] )
    )
VAR b =
    CALCULATETABLE ( VALUES ( 'Table'[Accountid] ), FILTER ( a, [Sum] >= 100 ) )
RETURN
    CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [Accountid] IN b ) )

Then put it to the matrix

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JRD]

Thanks so much for this answer! It's very similar to what I'm trying to do.

I also have a date slicer on the same page and right now when i adjust the slicer the account id row still shows up even when the sum that is shown in the table is less the sum i want to filter on. Eg. 100.

Ideally this row would only show up when the sum total is above the set threshold.

Is it possible to change the measure to also take the scope of time into account or is it an issue that must be solved in a different way?

I hope you can help but I understand if not of course. Thanks again