Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |