cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How to count number of yes/no from two separate columns in a table for pie chart?

 Workplace Does workplace have a water cooler? Does the workplace have a microwave? A Yes No B C Yes Yes D No Yes E F No No G Yes No H No No I Yes Yes J

So what I would like to achieve is have a pie chart visualisation that shows out of these 10 workplaces, how many have a "YES" in either having a water cooler or microwave.

Those workplaces without values were closed down.

So the pie chart should show 50% Yes (from A, C, D, G, I because they have "YES" in either columns) and 50% No (from B, E, F, H, J because they have "NO" in both columns/no values at all).

1 ACCEPTED SOLUTION
Community Support

Hi @matthewtjy ,

If there are many other columns like [Does workplace have a water cooler?]  or [Does the workplace have a microwave?] ,please kindly refer to @Tahreem24 's method by pivoting the original table.

Or if there are just the two columns, please add a column with "Yes" or "No" flag:

``````Yes or No =
var _combine= COMBINEVALUES("," , [Does workplace have a water cooler?] ,[Does the workplace have a microwave?])
return IF(CONTAINSSTRING(_combine,"Yes"),"Yes","No")``````

Then create Pie visual:

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

4 REPLIES 4
Community Support

Hi @matthewtjy ,

If there are many other columns like [Does workplace have a water cooler?]  or [Does the workplace have a microwave?] ,please kindly refer to @Tahreem24 's method by pivoting the original table.

Or if there are just the two columns, please add a column with "Yes" or "No" flag:

``````Yes or No =
var _combine= COMBINEVALUES("," , [Does workplace have a water cooler?] ,[Does the workplace have a microwave?])
return IF(CONTAINSSTRING(_combine,"Yes"),"Yes","No")``````

Then create Pie visual:

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

Super User

@matthewtjy try this:

Step1 : Go to power query editor and select Microwave and watercooler column then click on unpivot column under Transform tab.

Step 2: Close and Apply

Step 3: Create a measure like this:

Measure =  CALCULATE(count(WorkspaceTable[Workplace]),WorkspaceTable[Value]="Yes")
Step 4: Then take a pie chart and take this measure under values field and Attribute under Details field.

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard
Super User

@matthewtjy , Create a table with Two values yes and no. Then join it with both columns. One join will be inactive, that you can activate using userelationship  Then you can have measure

Count(Table[Workplace]) +calculate(Count(Table[Workplace]) ,USERELATIONSHIP(dim[Yes/No],Table[Does the workplace have a microwave?]) )

Helper I

Hi I'm sorry but how do i go about to create a table with Two values yes and no. Then join it with both columns?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors