PIE Chart - Counting Values

[Gasdetect40]

Good Day!

As seen in the pics below I want to count how 'Event Descriptions' there are and put them in a Pie Chart in Power BI. I have leanrt how to do it in Excel but with all my internet searching I'm failing to find anything to help me. Anyone got any ideas?

I just want the pie chart to refect how many times each gas alarm is seen.

Excel Sheet with Data and Pie ChartPortion of my POwer BI data

[TheoC · Accepted solution]

Hi @Gasdetect40 

If you go to canvas view in Power BI Desktop, select a Pie Chart.  Drag the Event Description column into the Legend space and then drag the Event Description into the Value space.  Make sure to click the down arrow on the Event Description in the Values section and click Count.

Hope this helps.

Theo 🙂

[v-eqin-msft · Accepted solution]

Hi @Gasdetect40 ,

Agree with @TheoC to simply use the summarize type of "Count" for Event type:

Or you could create a measure to calculate the count of each description:

Measure = CALCULATE( COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Event description]))

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.

[v-eqin-msft · Accepted solution]

Hi @TheoC ,

Of course! as you mentioned, COUNTROWS ( 'Table' ) is enough for this simple case. I'm just used to “fullfill” the measure in case there are some more complex situations. Sorry for the redundant operation😜

Best Regards,
Eyelyn Qin

[Whibley92]

How would you do this for multiple columns? The columns were originally words in one cell but I split by delimiter and now have 4 columns. Some are blank as there was a 4 word choice limit in the question form.

[TheoC]

Hi @Whibley92 

If you used "Split by Delimiter" then you are in Power Query.  With that in mind, you can use Power Query to "Pivot / Unpivot" the data you have and create one single column with multiple categories in that New Column.

• Select the four columns by holding down the Ctrl button and click on each column
• Then use your mouse and right-click one of the selected column names.
• Click the "Unpivot" option.

Now, go back to Canvas View in Power BI Desktop (i.e. out of Power Query).  Use the New Column as the category field, and you should be able to get the outcome you want.

Hope this helps.

Theo 🙂

[Whibley92]

Thanks yes I was worried about how it would affect other columns in the table, so I created a copy and then practiced on that.

Can you Pivot/Unpivot on selected columns within a large table?

[TheoC]

@Whibley92  yeah, absolutely you can do it. But, as you mentioned, for large tables with many columns it can lead to data redundancy and duplication.  For example, if you created a measure such as COUNTROWS before the Pivot / Unpivot in Power Query, you will likely find that it will be a different result to that after the Pivot / Unpivot.  This is the same with other aggregation fucnctions such as SUM. 

One way to ensure you don't run into these issues is by structuring your model appropriately.  So, for example, if you are working on a large table that has a range of categorical data (imagine an Employee Master File in a single table, for example), there are columns associated with 1) Address, 2) Bank Accounts, 3) Personal attributes, etc.  For each category, you could create a table that has the Employee Id, Address Line 1, Address Line 2, Suburb / Region, State, Country, Postcode.  Similarly, with teh Bank Account details you could have the Employee Id, Bank Account Name, BSB Number, Bank Account Number, SWIFT, etc.  

Hopefully the above provides some additional context!

Cheers mate.

Theo

[Gasdetect40]

Sorry everyone, I will get better at the 'reply as a solition' task. Thank you for all your help!

[TheoC · Accepted solution]

Hi @Gasdetect40 

If you go to canvas view in Power BI Desktop, select a Pie Chart.  Drag the Event Description column into the Legend space and then drag the Event Description into the Value space.  Make sure to click the down arrow on the Event Description in the Values section and click Count.

Hope this helps.

Theo 🙂

[v-eqin-msft · Accepted solution]

Hi @TheoC ,

Of course! as you mentioned, COUNTROWS ( 'Table' ) is enough for this simple case. I'm just used to “fullfill” the measure in case there are some more complex situations. Sorry for the redundant operation😜

Best Regards,
Eyelyn Qin

[TheoC]

Hi @v-eqin-msft 

Haha... I agree, measures are always fun to use and it makes sense in terms of more complex situations arising.  And your post was not redundant in any way, I just wanted to better understand the driver!!

Thanks heaps for explaining and all the best!

Theo 🙂

[v-eqin-msft · Accepted solution]

Hi @Gasdetect40 ,

Agree with @TheoC to simply use the summarize type of "Count" for Event type:

Or you could create a measure to calculate the count of each description:

Measure = CALCULATE( COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Event description]))

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.

[TheoC]

Hi @v-eqin-msft 

Thanks for the kind comments. 

With regard to your post, what has driven you to create a measure of that nature given that the Legend in the Pie Chart still needs to broken down using the Event Description field?  Would this not create unnecessary duplication / overkill?  

Thanks and look forward to better understanding the approach!  I would have thought, given that pie charts must be disected with something (i.e. the Event Description), if any measure was to be used, it would have just been "COUNTROWS ( 'Table' )" rather than integrating CALCULATE and ALLEXCEPT.

All the best!

Theo 🙂

[TheoC · Accepted solution]

Hi @Gasdetect40 

If you go to canvas view in Power BI Desktop, select a Pie Chart.  Drag the Event Description column into the Legend space and then drag the Event Description into the Value space.  Make sure to click the down arrow on the Event Description in the Values section and click Count.

Hope this helps.

Theo 🙂