How to divide specific column by other specific columns to get percentage

[DJIX12]

Say I have 7 columns of true/false, labeled as column 1, column 2, column 3, column 4, column 5, column 6, column 7

And say column 1,2,5 were true values and column 7 is false (other columns are irrelevant) 

What formula can I use to get a percentage of columns = true? Using something along the lines of true columns, column 1, 2, 5 divided by all relevant columns - columns 1,2,5,7 

in the example above it should return with a 75% 

[SpongYe]

@DJIX12 

use the COUNTIF and COUNT functions, similar to Excel. For example, if you have the true/false values in a collection called colData, and the columns are named as Column1, Column2, Column3, etc., you can use this formula:

COUNTIF(colData,Column1=true)+ COUNTIF(colData,Column2=true)+ COUNTIF(colData,Column5=true) /
COUNTIF(colData,Column1=true)+ COUNTIF(colData,Column2=true)+ COUNTIF(colData,Column5=true)+ COUNTIF(colData,Column7=true)

This will count the number of rows where Column1, Column2, and Column5 are true, and divide it by the number of rows where Column1, Column2, Column5, and Column7 are true. This will return 0.75 or 75%, as expected.

If you have any questions or feedback, please let me know. Have a great day! 😊

-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻

[DJIX12]

How does the syntax change if doesn't matter what the value of column 1,2,5,7 is? 

Additionally I assume "colData" is data source?

[SpongYe]

Hi @DJIX12 

If it doesn’t matter what the value of column 1, 2, 5, and 7 is, then you can use a simpler formula to calculate the percentage of columns that are true in PowerApps. You can use the CountRows function to count the number of rows in a collection, and then divide by the total number of columns in the collection. For example, you can write:

CountRows(colData) / Count(colData) * 100 & "%"

This formula will count the number of rows in your collection colData, which is the same as the number of columns that have any value (true or false). Then it will divide by the number of columns in the collection, which is 7 in your case. The result is then multiplied by 100 to get the percentage. The & operator concatenates the percentage sign to the end of the number.

If you have any questions or feedback, please let me know. Have a great day! 😊

-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻

[DJIX12]

Sorry I should've specified, I'm looking for total percentage of "true" in Columns 1,2,5,7 divided by columns 1,2,5,7 (regardless of value) 

end result is I want to find out total percentage of "true". Also my dataset has to be SharePoint and not a collection. (small dataset) 

[SpongYe · Accepted solution]

I see. So you want to calculate the percentage of true values in columns 1, 2, 5, and 7 out of the total number of values in those columns. One possible way to do this in PowerApps is to use the CountRows and Filter functions. For example, you can use this formula:

CountRows(
    Filter(
        <datasource>, 
        Column1 = true || 
        Column2 = true || 
        Column5 = true || 
        Column7 = true
     )
) / 
CountRows(
    Filter(
        <datasource>, 
        !IsBlank(Column1) || 
        !IsBlank(Column2) || 
        !IsBlank(Column5) || 
        !IsBlank(Column7)
    )
) * 100 & "%"

This formula will count the number of rows in your SharePoint list that have a true value in any of the columns 1, 2, 5, or 7, and divide it by the number of rows that have a non-blank value in any of those columns. Then it will multiply by 100 and add a percentage sign at the end.

If you have any questions or feedback, please let me know. Have a great day! 😊


-----------------------
PowerYsa Power Platform Enthusiast [LinkedIn] | [Youtube]

I love to share my knowledge and learn from others. If you find my posts helpful, please give them a thumbs up 👍 or mark them as a solution ✔️. You can also check out my [@PowerYSA] for some cool solutions and insights. Feel free to connect with me on any of the platforms above. Cheers! 🍻