Calculating based on a condition

[Anonymous]

Hello All,

    I have a table on whick I want to create a calculated column. The table looks like below:-

The calculated column's calculation is:- sum(AllThatHave'1')/Count(AllThatAreNonEmpty). 

Considering the above data it is :- 11/12 = 0.916667

If we convert it to percentage we get 91.67%

I know the calculation but I am struggling to write a DAX formula for this. Please can anyone help me to write an appropriate DAX formula for the above calculation.

Thank You in Advance!

[Anonymous · Accepted solution]

Hi MedhaTrivedi,

You can do it by unpivot your columns into Rows and calculate 1s and blanks in Power BI.

Go to your edi queries and unpivot all the columns into Rows. Your resultset should be like,

City	Activity	Value
A	Col1
B	Col1	1
A	Col2	1
B	Col2
A	Col3
B	Col3
A	Col4	1
B	Col4	1
A	Col5	1
B	Col5	1
A	Col6	1
B	Col6	1
A	Col7	1
B	Col7

Then easily find out how many 1s and blanks from the value column.

=DIVIDE(Sum(Table[Value]), CALCULATE(Count(Table[Value]), FILTER(Table, Table[Value] <> 1))

Hope this information helps you a lot.

Regards,

Pradeep