Powerbi Sum values and filter results

[Schifo78]

Hi I'm really struggling with this, I have a series of table rows each represent a project with a value, a client and a status. I'm trying to create a measure which filters clients where the sum of all their projects that have a 'live' status is greater than 50,000 but less than 200,000.  Reason for this is that I want a chart that only has clients whose total value of projects falls within this range. Any help greately appreciated!

So the ProjectTable data looks like this:

Project

Value

Status

Client

[v-kkf-msft · Accepted solution]

Hi @Schifo78 ,

Please try the measure.

Measure = 
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Client] ), 'Table'[Status] = "live" )
    )
RETURN
    COUNTROWS (
        FILTER ( 'Table', 'Table'[Status] = "live" && _sum >= 50000 && _sum <= 200000 )
    )

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

[Shreeram04]

Hi @Schifo78,

Please find the below screenshot and image for your reference.

Measure:  

    Measure 15 = CALCULATE(SUM(Sheet1[Amount]),FILTER(Sheet1,Sheet1[Amount]>40),FILTER(Sheet1,Sheet1[Amount]<90))

Total amount =2311 but the value between 40 and 90 is 1182.

Did I answer your question? Mark my post as a solution!

Thanks,

[Schifo78]

Hi @Shreeram04 

I've just tried your measure and that's giving me the same as what I managed to acheive, that is summing only projects where the value is greater than and less than.

E.g.

Its summing 

Client A Project A value 60,000

Client A Project B value 70,000

But not 

Client A Project C value 205,000

What I need is to return only Clients that have a total value for all thier projects between those two values, rather than only summing projects that fall between those two values. So in this use case Client A would be filtered out as the total value of thier three projects is above 200,000.

Thanks