Sum the aggregate of text columns

[beebul]

I have a SharePoint list that I'm collecting data with from an InfoPath form.

Unfortunately the list was set up to record text values (yes/ no) rather than numeric.

I need to be able to total each column where the value is 'yes'

Please see screenshot of data and the second screenshot of the desired output.

Any help much appreciated - thanks!

Desired Results:

[Greg_Deckler]

So, typically this is something like:

CALCULATE(SUM([Column]),[Another Column]="Yes")

[beebul]

Do i need to add the total column into my SharePoint list then to use it within Power BI?

ie. Ceremony 1 | Ceremony 1 Total | Ceremony 2 | Ceremony 2 Total | ... etc

[Greg_Deckler · Accepted solution]

No, just create a measure like this:

Measure = CALCULATE(COUNT([Ceremony 1]),[Ceremony 1] = "yes")

Now, if they come through as logical values into Power BI, you may have to use COUNTA instead of COUNT.

[beebul]

Ah I got it to work:

Ceremony 1 = CALCULATE(COUNTA('Query1'[Ceremony1]), 'Query1'[Ceremony1] = { "yes" }) 

Ceremony 2 = CALCULATE(COUNTA('Query1'[Ceremony2]), 'Query1'[Ceremony2] = { "yes" })

Ceremony 3 = CALCULATE(COUNTA('Query1'[Ceremony3]), 'Query1'[Ceremony3] = { "yes" })

etc...

Thanks!

[beebul]

Hi smoupre..

That syntax is wrong .. tried Count and CountA