The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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:
Solved! Go to 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.
So, typically this is something like:
CALCULATE(SUM([Column]),[Another Column]="Yes")
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.
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
User | Count |
---|---|
86 | |
84 | |
35 | |
35 | |
35 |
User | Count |
---|---|
94 | |
79 | |
63 | |
55 | |
52 |