Summing "True" values then dividing it by total values

[Anonymous]

Hi! I am quite new to Power BI and was hoping you could help me:

I need to sum the unique values in a column that contains the value "True" - so I want to get a number telling me how many "True values" that column has. This column contains the values "True" and "False". Next, I need to divide the number of total "True" by the total amount of "True" and "False" values. 

So, what I need is something like "Total number of True / Total number of values". This is to get a percentage of how many "True" values there are of the grand total. 

Can you please help me with writing an expression or the best way of solving this?

Thanks!

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

If your data type is "True/False", you can create a measure as follows.

Measure = 

var x1=COUNTROWS(FILTER('Sheet6',[value]=TRUE()))

var x2=COUNTROWS('Sheet6')

return

DIVIDE(x1,x2,0)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tahreem24]

@Anonymous ,

You can try this logic:

Measure =

VAR True_ = CALCULATE(COUNT(Table[True/False]),FILTER(Table,Table[True/False]="TRUE"))

VAR Total_ = COUNT(Table[True/False])

RETURN True_ / Total_

[Anonymous]

Hi again, thank you for your answer.

The measure is not working since it states that the "DAX does not support the value of type True/False and the value Text. Try using Value or Format

[Pragati11]

Hi @Anonymous ,

You haven't shared any sample data, so I am guessing the column names. You can use the following DAX to create a measure as follows:

Proportion = 

var t1 = CALCULATE(COUNT(tablename[col1]), tablename[col1] = "True")

var t2 = COUNT(tablename[col1])

RETURN

DIVIDE(t1, t2, 0)

In the above DAX replace tablename[Col1] with your table name and the relevant column name.

After this measure is created, chnage it's data format to percentage.

Thanks,

Pragati

[Anonymous]

Hi, thank you!

This does not seem to work because the DAX states that it does not support the value of True/False and the value type Text. Try using VALUE or FORMAT.

Any suggestions?

[Pragati11]

Hi @Anonymous ,

Change the DAX expression as follows:

Proportion = 

var t1 = CALCULATE(COUNT(tablename[col1]), tablename[col1] = TRUE())

var t2 = COUNT(tablename[col1])

RETURN

DIVIDE(t1, t2, 0)

Thanks,

Pragati

[Anonymous]

Hi again,

The error message I am getting now is "COUNT does not function with the value of type BOOLEAN"

[Pragati11]

Hi @Anonymous ,

Can you try modifying the data-type of your column with True/False values to TEXT type and then try the following DAX:

Proportion = 

var t1 = CALCULATE(COUNT(tablename[col1]), tablename[col1] = "True")

var t2 = COUNT(tablename[col1])

RETURN

DIVIDE(t1, t2, 0)

You are getting this error because the data-type for this column is Boolean type.

Thanks,

Pragati

[Anonymous]

I used COUNTA instead of COUNT and it seemed to work, it is now giving me values and counting how many TRUE instances there are. However I now need to sum all of these values. It is now giving me all 1`s for how many True there are, which is great, but I want to be able to summarize all these 1`s. Any suggestions?

[amitchandak]

@Anonymous ,

divide(calculate(countrows(table), filter(Table, [true false column])), countrows(table))

or

divide(calculate(countrows(table), filter(Table, [true false column] = "true")), countrows(table))

[Anonymous]

Hi, thank you!

This does not seem to work because the DAX states that it does not support the value of True/False and the value type Text. Try using VALUE or FORMAT.