cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MedhaTrivedi Frequent Visitor
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:-

p1.PNG

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
Pauwnrajpp Member
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,

 

CityActivityValue
ACol1 
BCol11
ACol21
BCol2 
ACol3 
BCol3 
ACol41
BCol41
ACol51
BCol51
ACol61
BCol61
ACol71
BCol7 

 

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 1
Pauwnrajpp Member
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,

 

CityActivityValue
ACol1 
BCol11
ACol21
BCol2 
ACol3 
BCol3 
ACol41
BCol41
ACol51
BCol51
ACol61
BCol61
ACol71
BCol7 

 

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