Forum Discussion
Helper IIIHow to create a chart for date count and values sum
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.
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" ) )
4 Replies
diegolimaThank you all so much for your help
- Greg_Deckler
Community Champion
My pleasure! 🙂
- Greg_Deckler
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" ) ) 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.
