cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculating based on a condition

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Calculating based on a condition

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,

Member

## Re: Calculating based on a condition

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,