Pie Chart not grouping same values

[Anonymous]

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?

[mr-dang]

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.

[mr-dang · Accepted solution]

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

[Anonymous]

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 · Accepted solution]

---

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

[Anonymous]

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]

@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))

[Anonymous]

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

Thanks a lot!