Calculate DAX formula

[Anonymous]

I am new to power bi. I have an excel formula 

=IFERROR(COUNTIF(N3:N41,">="&$P$42)/(COUNTA(N3:N41)-COUNTIF(N3:N41,"")),"") 

Where &$P$42 value is 2.

N3:N41 cell values is average of all other cells. I have to find the maturity % which gets me to 23.08%. how to calculate?

[Anonymous]

I have created measure:

Maturity = CALCULATE(COUNTROWS(SecurityReport),IF(SecurityReport[Score]>2,1,0))/COUNTROWS(SecurityReport)

Now for calculating the Maturity% = 23.08%

I need the measure "Maturity" in the DAX calculation. I am trying to use the similar query but its returning error:

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

Measure1 = CALCULATE(COUNTROWS(SecurityReport),IF(SecurityReport[Maturity]>2,1,0))/COUNTROWS(SecurityReport)

As SecurityReport[Maturity] is the calculated measure, if function is not accepting the measure. How should it be replaced?

Help needed!

[Ashish_Mathur]

Hi,

I am not very clear about your requirement but try this measure

=CALCULATE(COUNTROWS(SecurityReport),SecurityReport[Score]>2)/COUNTROWS(SecurityReport)

This itself should return the % to you.  What else do you need to calculate?

[Anonymous]

Hi Ashish,

Yes I created that measure (as you mentioned). But I have to calculate the % i.e 23.08% in N42 column (excel sheet). N3:N42 column is the average of all scores (in all rows). 

So first I have created a measure of Average=

Average= AVERAGEX(SecurityReport,SecurityReport[Score])

Then use this measure again in a new measure, I was trying to use the similar syntax (like you mentioned) to acheive 23.08%, but its not displaying any data:

Maturity%= COUNTX(SecurityReport,IF(AVERAGEX(SecurityReport,SecurityReport[Score])>=2,1,0)/COUNTROWS(SecurityReport))

I have to get to 23.08% which is N42 column. Help appreciated!

[Ashish_Mathur]

Hi,

Share some data (which i can paste in MS Excel) and show the expected result.

[Ashish_Mathur]

Hi,

I cannot understand much from your pasted data.  Also, in column N, count of values greater than 2 are 3 and the count of all values is 7.  The % should be 3/7=42%.

It will really help if you can share data in a PBI file.

Thank you.

[Greg_Deckler]

Translating Excel to Power BI can be tricky sometimes. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, let me see if I understand your formula ignoring the IFERROR for now.

Your numerator is that you are counting up the rows in your column whose value is >= 2

Your denominator counts up all of the rows that are now empty and subtracts the rows that are empty.

You normally replace COUNTIF type statements with a CALCULATE or a COUNTX. For example, both of these will replace your numerator:

Measure = COUNTX(FILTER('Table',[Maturity Bar]>=2),[Maturity Bar])

Measure1 = CALCULATE(COUNT([Maturity Bar]),FILTER('Table',[Maturity Bar]>=2)