Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
beebul
Regular Visitor

Sum the aggregate of text columns

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!

 

yes-no.jpg

 

Desired Results:

aggregates.jpg

1 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.

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Community Champion
Community Champion

So, typically this is something like:

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

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.

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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!

Hi smoupre..

 

That syntax is wrong .. tried Count and CountA

 

syntax.jpg

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.