SUM of IF Statement Results

[Anonymous]

Hello Everyone,

Would someone be able to know how I could sum my if statement measure results in the below for "Met" category:

Eventually, I would like build a measure that gives the percentage of "Met" / All days.

When I attempt to sum the IF statement result ("Met") only ( >70%), the DAX formula bar present the following error message:

"a function 'placeholder' has been used in a true/false expression that is used as a table filter. This is not allowed." 

Is there any workaround on this problem?

Thank you in advance,

Mohammed

[v-rongtiep-msft]

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case and help more people find the answer please. Thanks in advance.

Best Regards

Community Support Team _ Polly

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

[v-rongtiep-msft]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create a measure.

measure =
VAR _today =
    MONTH ( TODAY () )
VAR month_ =
    MONTH ( MAX ( 'Table'[Estates] ) )
VAR _thisyear =
    YEAR ( TODAY () )
VAR date_year =
    YEAR ( MAX ( 'Table'[Estates] ) )
VAR acount_met =
    CALCULATE (
        COUNTAX ( ALL ( 'Table' ), 'Table'[met] ),
        FILTER ( ALL ( 'Table' ), _today = month_ && _thisyear = date_year )
    )
RETURN
    DIVIDE ( MAX ( 'Table'[met] ), acount_met )

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output with more details.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

@amitchandak Thank you for your response, it is working an am no longer receiving the previous error!

However there is a little problem here where "Met" scores are divided by the entire month days.

Instead, how would I be able to lock the denomenator based on the given days presented in the dashboard?

i.e.(denomenator should be the # of business days in the month to date).

for better illustrution, my highlighted value should be (1/7 ~ 14%) instead of 3.23%:

Thank you kindly,

Regards,

Mo

[amitchandak · Accepted solution]

@Anonymous , a measure like

divide(countx(filter(values('Date'[date]) , [Percent Measure] > .7),[Date]), count('Date'[date]))

or

divide(countx(filter(values('Date'[date]) , [Percent Measure] > .7),[Date]), calculate(count('Date'[date]), allselected()))