cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Pie Chart not grouping same values

So as you can see in the picture, there are two values that I'm sorting by. 

 

Source of File (Email, Facebook, etc)'

Date it Came

 

The pie chart is creating a slice for every single file instead of grouping all files that came from email for instance, into one slice. 

 

The date I want to add so the pie chart is filterted based on the users date range selection.  

 

How can I get the pie chart to group the information?

piechart.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
mr-dang
Level 10

Re: Pie Chart not grouping same values

If you want to filter the pie chart by date too, I would add a datepicker control. When the user selects a date, you want the Items property of the pie chart to respond by filtering out only the data from that date.

 

In that case, you would add a Filter around Case:

 

AddColumns(
    GroupBy(
        Filter(Case,ArrivalDate=DatePicker1.SelectedDate),
        "CSource_","CSource_1")
    "countsource",CountRows(CSource_1)
)

This means, "Show only the CSource_ data for records whose ArrivalDate matches the date selected in DatePicker1. Group them according to source as described in the last post, then show the size of each source according to the calculated column, countsource."

Microsoft Employee
@8bitclassroom

View solution in original post

mr-dang
Level 10

Re: Pie Chart not grouping same values

 


@Anonymous wrote:

How can I add so the Chart shows the actual number of the items?


Hi,

If you mean how do you show the count on the pie chart, you will need to add a helper column to show the actual text you want. Unlike Excel, the pie chart object does not have a property for showing both the name of the slice and the number value for the slice, so you have to make it yourself.

 

Normally you can add as many columns as you want inside one AddColumns formula. But since the next column relies on the value countsource, you will need to close off its calculation. Nest what you have inside another AddColumns formula.

AddColumns(
    AddColumns(
        GroupBy(
            Filter(Case,ArrivalDate=DatePicker1.SelectedDate),
            "CSource_","CSource_1"),
        "countsource",CountRows(CSource_1)),
    "label",CSource_ & Char(10) & countsource
)

From here, format what you want to show in label. Edit the part in blue to what you would like. If you want to show a percentage, you will divide by the number of all records:

CSource_ & Char(10) & countsource/CountRows(Case)

Use Text() to change how many decimals you want. It works the same way as it does in Excel:

CSource_ & Char(10) & Text(countsource/CountRows(Case),"##.0")

Then set the pie chart to show the 'label' column you just created.

Microsoft Employee
@8bitclassroom

View solution in original post

7 REPLIES 7
mr-dang
Level 10

Re: Pie Chart not grouping same values

Hi,

There is an easy fix for this. You will need to use the GroupBy() function around your datasource so that distinct values in the CSource_ are grouped together. This will result in one slice for Email, one slice for Facebook, etc.

 

Then you might need to use AddColumns() to return a count of the items in each. The end result might look something like this in the Items property of your Pie Chart:

 

AddColumns(
    GroupBy(Case,"CSource_","CSource_1")
    "countsource",CountRows(CSource_1)
)

This means, "Group the Case datasource by distinct values in the column CSource_. Then count up how many records are in the resulting grouped table for each category (email, fb, etc.)."

 

You would then set the Label property to CSource_1 and the Series property to countsource, or whatever you choose to name them.

Microsoft Employee
@8bitclassroom
mr-dang
Level 10

Re: Pie Chart not grouping same values

If you want to filter the pie chart by date too, I would add a datepicker control. When the user selects a date, you want the Items property of the pie chart to respond by filtering out only the data from that date.

 

In that case, you would add a Filter around Case:

 

AddColumns(
    GroupBy(
        Filter(Case,ArrivalDate=DatePicker1.SelectedDate),
        "CSource_","CSource_1")
    "countsource",CountRows(CSource_1)
)

This means, "Show only the CSource_ data for records whose ArrivalDate matches the date selected in DatePicker1. Group them according to source as described in the last post, then show the size of each source according to the calculated column, countsource."

Microsoft Employee
@8bitclassroom

View solution in original post

Anonymous
Not applicable

Re: Pie Chart not grouping same values

Thanks for the assistance!  

 

When trying this method, I'm receving an error stating "Expected an operator (e.g., +,*,&&)"

 

 

Edit:  Nevermind, I got it to work!  Was missing a "," before "countsource"  How can I add so the Chart shows the actual number of the items?

mr-dang
Level 10

Re: Pie Chart not grouping same values

 


@Anonymous wrote:

How can I add so the Chart shows the actual number of the items?


Hi,

If you mean how do you show the count on the pie chart, you will need to add a helper column to show the actual text you want. Unlike Excel, the pie chart object does not have a property for showing both the name of the slice and the number value for the slice, so you have to make it yourself.

 

Normally you can add as many columns as you want inside one AddColumns formula. But since the next column relies on the value countsource, you will need to close off its calculation. Nest what you have inside another AddColumns formula.

AddColumns(
    AddColumns(
        GroupBy(
            Filter(Case,ArrivalDate=DatePicker1.SelectedDate),
            "CSource_","CSource_1"),
        "countsource",CountRows(CSource_1)),
    "label",CSource_ & Char(10) & countsource
)

From here, format what you want to show in label. Edit the part in blue to what you would like. If you want to show a percentage, you will divide by the number of all records:

CSource_ & Char(10) & countsource/CountRows(Case)

Use Text() to change how many decimals you want. It works the same way as it does in Excel:

CSource_ & Char(10) & Text(countsource/CountRows(Case),"##.0")

Then set the pie chart to show the 'label' column you just created.

Microsoft Employee
@8bitclassroom

View solution in original post

Anonymous
Not applicable

Re: Pie Chart not grouping same values

That worked.  I also followed your advice on the Date selector but added two date pickers so the user can select a range.

 

 

This is the code that works:

 

AddColumns(GroupBy(Filter(Case,ArrivalDate>DatePicker2.SelectedDate,ArrivalDate<DatePicker3.SelectedDate),"CSource_","Source"),"countsource",CountRows(Source))

 

The problem is that if the dates that the user selects is for example 6/1/2017 and 6/30/17 the code excludes those two days. 

 

So I tried putting a "-1" and a "+1" like this:

 

AddColumns(GroupBy(Filter(Case,ArrivalDate>DatePicker2.SelectedDate-1,ArrivalDate<DatePicker3.SelectedDate+1),"CSource_","Source"),"countsource",CountRows(Source))

But once I put this code the pie chart disappears and I have an error saying "An unknown error occured" 

mr-dang
Level 10

Re: Pie Chart not grouping same values

@Anonymous wrote:

 

The problem is that if the dates that the user selects is for example 6/1/2017 and 6/30/17 the code excludes those two days. 

 

So I tried putting a "-1" and a "+1" like this:

 

AddColumns(GroupBy(Filter(Case,ArrivalDate>DatePicker2.SelectedDate-1,ArrivalDate<DatePicker3.SelectedDate+1),"CSource_","Source"),"countsource",CountRows(Source))

But once I put this code the pie chart disappears and I have an error saying "An unknown error occured" 


 

Hi,

All you have to do is change it to greater than and equal to and less than and equal to, and it will be inclusive:

AddColumns(GroupBy(Filter(Case,ArrivalDate>=DatePicker2.SelectedDate,ArrivalDate<=DatePicker3.SelectedDate),"CSource_","Source"),"countsource",CountRows(Source))
Microsoft Employee
@8bitclassroom
Anonymous
Not applicable

Re: Pie Chart not grouping same values

Seriously don't know why that didn't even cross my mind.... 

 

 

Thanks a lot!

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,586)