Forum Discussion
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!
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
1 Reply
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