Count number of projects with balance sum of zero

[Anonymous]

Hi 

Seeking assistance from an experienced Power BI user,

I am trying to count the number of the projects which have WIP Balance (sum('WIP Additions'[Addition Amount])

of nil (measure to be named Finished Projects).  Ultimately I am trying to work out how many projects have been finished (has nil balance) and which department (Parks, Water, or Transport) these projects belong to.  I have tried CALCULATE with COUNT and FILTER, but none of the formulas I've tried worked.

Thanks for your assistance.

Here is the link to the files.  

https://drive.google.com/file/d/17lBv76rF2B4vHolqjEhklXOrr1o5TEoc/view?usp=sharing

[OwenAuger · Accepted solution]

Hi @Anonymous 

Below are a couple of ways you could rewrite your Finished Projects measure. I have adjusted the measure so that if filters values of 'Project Detail'[Project Number] as it is generally preferable to filter on dimension tables. However, you could change 'Project Detail'[Project Number] to 'WIP Additions'[Project Number] and the measures would still work 

Finished Projects = 
CALCULATE (
    DISTINCTCOUNT('Project Detail'[Project Number]),
    FILTER(
        VALUES ( 'Project Detail'[Project Number] ),
        [WIP Balance] = 0
    )
)

Or since you are counting projects, you could use this slightly simpler measure:

Finished Projects = 
COUNTROWS (
    FILTER(
        VALUES ( 'Project Detail'[Project Number] ),
        [WIP Balance] = 0
    )
)

A side note: I see a number of projects have very small WIP Balance values (positive or negative) so you may want to test for a WIP Balance that is close to zero. To do this you could adjust the condition to something like this:

Finished Projects within threshold = 
VAR Threshold = 0.001
RETURN
    COUNTROWS (
        FILTER(
            VALUES ( 'Project Detail'[Project Number] ),
            ABS ( [WIP Balance] ) < Threshold
        )
    )

Regards,

Owen

[Anonymous]

Hi @OwenAuger 

Thanks for your help.  And you were right the projects with tiny balances will create some issues for me, now I need to work out what is the best way to prevent this from happening.  Thanks again for your help.