¿How i can calculate average of total tickets by date to show in table?

[JUSEGAPO]

Dear all,

I need help to can calculate average of qty of tickets by date and show in a table with some filters: 

So, i have it data:

TICKET	PLAN	DATE	CATEGORY	TYPE
6-00000819	PLAN 1	2017-07-06	COMBUSTIBLE	ADITIONAL
6-00000810	PLAN 3	2017-07-06	MAYBE COMBUSTIBLE	ADITIONAL
6-00000802	PLAN 3	2017-07-06	COMBUSTIBLE	ADITIONAL
6-00000797	PLAN 2	2017-07-06	COMBUSTIBLE
6-00000791	PLAN 3	2017-07-06	NO COMBUSTIBLE	ADITIONAL
6-00000787	PLAN 3	2017-07-06	MAYBE COMBUSTIBLE	ADITIONAL
6-00000777	PLAN 1	2017-07-06	MAYBE COMBUSTIBLE
6-00000822	PLAN 1	2017-07-07	COMBUSTIBLE
6-00000812	PLAN 1	2017-07-08	MAYBE COMBUSTIBLE	ADITIONAL
6-00000813	PLAN 2	2017-07-08	MAYBE COMBUSTIBLEADITIONAL
6-00000804	PLAN 1	2017-07-08	COMBUSTIBLE
6-00000790	PLAN 2	2017-07-08	NO COMBUSTIBLE	ADITIONAL
6-00000780	PLAN 1	2017-07-08	MAYBE COMBUSTIBLE	ADITIONAL
6-00000815	PLAN 1	2017-07-09	NO COMBUSTIBLE	ADITIONAL
6-00000796	PLAN 3	2017-07-09	COMBUSTIBLE	ADITIONAL
6-00000786	PLAN 1	2017-07-09	MAYBE COMBUSTIBLE	ADITIONAL
6-00000781	PLAN 1	2017-07-09	MAYBE COMBUSTIBLE
6-00000817	PLAN 1	2017-07-10	COMBUSTIBLE	ADITIONAL
6-00000806	PLAN 3	2017-07-10	MAYBE COMBUSTIBLE	ADITIONAL
6-00000793	PLAN 3	2017-07-10	NO COMBUSTIBLE	ADITIONAL
6-00000784	PLAN 1	2017-07-10	MAYBE COMBUSTIBLE	ADITIONAL

I need to get average of tickets created by date range, in this case, AVG of tickets created from 2017-07-06 to 2017-07-10 split by PLAN and with filter CATEGORY<> "COMBUSTIBLE" and TYPE="ADITIONAL", the expected table is:

PLAN	TOTAL	AVERAGE
PLAN 1	5	1
PLAN 2	2	0,4
PLAN 3	5	1

Thanks in advanceee!!! 🙂

[Phil_Seamark · Accepted solution]

Hi @JUSEGAPO

I created a new calculated table using the following code and got pretty close...

NEW TABLE  = 
GROUPBY(
        GROUPBY(
            ADDCOLUMNS(
                FILTER(
                    'Table2',
                    'Table2'[CATEGORY]<>"COMBUSTIBLE" 
                    && Table2[TYPE]="ADITIONAL"
                    ),
                "C",1),
            Table2[PLAN],
            Table2[DATE],
            "TOTAL",SUMX(CURRENTGROUP(),[C])            
            )
        ,[PLAN],
        "TOTAL",SUMX(CURRENTGROUP(),[TOTAL]),
        "AVERATE",AVERAGEX(CURRENTGROUP(),[TOTAL])
        )

I get different figures to you for both TOTAL and AVERAGE but that's because I was following your rules exactly.  In any case it should be easy to tweak.