How to create a chart for date count and values sum

[diegolima]

Hello, I needed to create a chart for the error control, but I don't know how to do it, this control needs to analyze the days of the month and select the ones that have more errors, and then calculate this number of days with error to separate in the error category.

Data Base

DEVICE	ERROR	DATE
1	6	01/01/2020
2	170	01/01/2020
3	1	01/01/2020
1	5	02/01/2020
2	1	02/01/2020
3	1	02/01/2020
1	7	03/01/2020
2	1	03/01/2020
3	1	03/01/2020
1	8	04/01/2020
2	1	04/01/2020
3	1	04/01/2020
1	10	05/01/2020
2	1	05/01/2020
3	1	05/01/2020

DEVICE	DATE COUNT	TOTAL ERROR
1	5	36
2	1	170
3	0	0

The Devices with error from to one for day will don't be count

CATEGORY OF ERROR

DATE COUNT > 3 AND TOTAL ERROR >30 <100 = YELLOW
DATE COUNT > 3 OR TOTAL ERROR > 100 = RED
DATE COUNT < 3 OR TOTAL ERROR < 30 = GREEN

Please, can anyone help me?

Thank you all.

[diegolima]

Thank you all so much for your help

[Greg_Deckler]

My pleasure! 🙂

[Anonymous]

Convert your data to an Excel data table Insert > Table (not required, but it makes it easier for data maintenance and updating long-term).
With your data table selected, insert a pivot table Insert > Pivot Table.
Setup your pivot table:
Rows = SentDate
Columns = FromNumber
Values = Count of FromNumber (may default to Sum, just change thru Value Field Settings dialog).
Insert your chart Insert > Charts > 2d Column Chart. By default, this will create a pivot chart, not a regular chart.
Format to taste.

[Greg_Deckler · Accepted solution]

Should be:

Table =
  ADDCOLUMNS(
    SUMMARIZE(
      'Table',
      [DEVICE]
      "DATE COUNT",COUNTROWS('Table')
      "TOTAL ERROR",SUM('Table'[ERROR])
    ),
    "CATEGORY OF ERROR",
      SWITCH(TRUE(),
        [DATE COUNT] > 3 && [TOTAL ERROR] > 30 && [TOTAL ERROR] < 100,"YELLOW",
        [DATE COUNT] > 3 && [TOTAL ERROR] > 100,"RED",
        "GREEN"
      )
  )