Filter by the sum of a column where value of another column meets a condition

[Anonymous]

I'm not sure if I can do this with a measure, or if I need to Group in Query Editor. 

But if I can do it with a measure, great. 

I have a table containing Invoice ID, and Invoice Amount.

There can be more than one row with the same Invoice ID, with different amounts. 

So to get the total Invoice Amount, you have to sum up that column by distinct Invoice ID. 

I need a measure which filters the above, but where the total Invoice Amount is over $20k.

So at the moment I have done =CALCULATE(DISTINCTCOUNT('Table'[Invoice ID], FILTER('Table'[Invoice Amount]>20000))

But this only pulls rows which are over $20k not the sum of the Invoice ID. 

I hope this makes sense!

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you never talked about having the count of distinct invoice ids whicht amount is over 20k

Here the measure that should give you the number only

CountIDsOver20K = COUNTROWS(
    FILTER(  
        ADDCOLUMNS( 
            SUMMARIZE(
                Invoices;
                Invoices[Invoice ID]
            );
            "Over20K";
            calculate(
                sum(Invoices[Amount]);
                filter(
                    ALL(Invoices);
                    Invoices[Invoice ID]= earlier(invoices[Invoice ID])
                )
            )
        )
        ;[Over20K]>20000
    )
)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce the scenrio.

Then you may create the measure as follows.

Num Of over 20k = 
COUNTROWS (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table' ),
            'Table'[Invoice ID],
            "Total", SUM ( 'Table'[Invoice Amount] )
        ),
        [Total] > 20000
    )
)

Result:

If I misunderstand your thought, please show me your sample data and expected result. Do mask sensitive data before uploading. I am glad to solve the problem for you.

Best Regards
Allan

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

[Jimmy801]

Hello @Anonymous 

try out this measure

Invoices over 20k = CALCULATE(sum(Invoices[Amount]);Filter(Invoices;sum(Invoices[Amount])>19000))

this is the result

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun
Jimmy

[az38]

Hi @Anonymous 

try SUMX function

= SUMX(FILTER('Table','Table'[Invoice Amount]>20000),'Table'[Invoice Amount])

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

@amitchandak this returns an error 'The True/False expression does not specify a column. Each true/false expressions used as a table filter expression must refer to exactly one column.

@az38 this just gives me the sum of invoices over $20k but not the number of invoices over $20k.

@Jimmy801 My measure would be 'Number of Invoices over 20k USD =' so I just want a figure, which is the distinct count of Invoice ID where the SUM of Invoice Amount (collectively, not by row) is over 20k. 

[amitchandak]

Try

measure =
var _sum = sum('Table'[Invoice Amount])
return 
calculate(countx(values('Table'[Invoice ID]),'Table'[Invoice ID]),filter('Table',_sum>20000))

Or
measure =
var _sum = sum('Table'[Invoice Amount])
return 
calculate(countx(values('Table'[Invoice ID]),distinctcount('Table'[Invoice ID])),filter('Table',_sum>20000))

[Anonymous]

Hi @amitchandak 

Both of these are giving me an invoice amount. It isn't actually the total invoice amount of all invoices over 20k so i'm not sure what it's calculating, but I actually just want the total number of Invoice IDs where the sum of Invoice Amount is over 20k.

I'm trying to fiddle with your measure but so far it's not happy with me.

[amitchandak]

Values is used to get distinct values.

The first measure is a sum, the second measure is using values. Means data will group ate invoice id level. there we can use the filter on the values. As data is grouped at invoice id. we can simply count it

[Anonymous]

Ok but neither of the measures give me the number I need, and I can't seem to generate an aggregate of Invoice Amount by distinct Invoice ID. I can do this by grouping in Query Editor but this is an extra table I was hoping to achieve in a measure. 

Thanks for looking at this anyway!

[amitchandak]

I tested a similar scenario and found only work when the group by is the same as the filter level.

But this one works

CALCULATE(COUNTX(filter(SUMMARIZE(Sales,'Date'[date],"s1",SUM(Sales[Sales Amount])),[s1]>120),[s1])

In your case date is invoice id and once data grouped at invoice id, just count is needed. Hope this will work.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you never talked about having the count of distinct invoice ids whicht amount is over 20k

Here the measure that should give you the number only

CountIDsOver20K = COUNTROWS(
    FILTER(  
        ADDCOLUMNS( 
            SUMMARIZE(
                Invoices;
                Invoices[Invoice ID]
            );
            "Over20K";
            calculate(
                sum(Invoices[Amount]);
                filter(
                    ALL(Invoices);
                    Invoices[Invoice ID]= earlier(invoices[Invoice ID])
                )
            )
        )
        ;[Over20K]>20000
    )
)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Thanks everyone for your help on this, DAX still eludes me at every turn! 
@Jimmy801 this works wonderfully, I have since tried to create a second measure adapting yours, to sum the total Invoice Amount associated with the invoices over $20k.

The measure is called Total value of claims over 20,000 USD

Sadly I have failed in my attempts, not being sure which part of your measure to change from Count of [Invoice Id] to sum of [Invoice Amount]

Can you accept the next challenge??

[Jimmy801 · Accepted solution]

Hello @Anonymous 

try out this (not tested)

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
    )
)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

@Jimmy801 once again you have excelled yourself. This works! I was way out on my attempt - I had the Calculate(Sum at the top, but got stuck quite quickly after that. 

[Jimmy801]

Hello @Anonymous 

I appreciate this feedback. Thank you.

Bye

Jimmy

[az38]

@Anonymous 

so, COUNTX

= COUNTX(FILTER('Table','Table'[Invoice Amount]>20000),'Table'[Invoice Amount])

do not hesitate to give a kudo to useful posts and mark solutions as solution

[amitchandak]

Try

measure =
var _sum = sum('Table'[Invoice Amount])
return 
calculate(countx(values('Table'[Invoice ID]),'Table'[Invoice ID]),_sum>20000)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601