Create new table containing the average of another table's column, per category

[DavidJ]

Hi all,

I've spent hours trying to solve this seemingly simple problem and reading previous solutions, which normally works, but this time I just can't find the right answer.

I have one table containing a daily percentage for a number of categories:

date	category	daily percentage
01-Jan	A	76%
02-Jan	A	84%
03-Jan	A	72%
01-Jan	B	23%
02-Jan	B	52%
03-Jan	B	45%
01-Jan	C	50%
02-Jan	C	82%
03-Jan	C	75%

I want to create a new table that summarises the average of this percentage, per category

category	summary percentage
A	77.33%
B	40%
C	69%

I've tried a few different solutions but the one below is where I am currently:

summary table = 
SUMMARIZE(
    SELECTCOLUMNS('table 1',
    "category",
    'table 1'[category],
    "summary_percentage",
    AVERAGE('table 1'[daily percentage])),
    [category],[summary_percentage]
)

What returns is the average percentage of the total [daily percentage], ignoring the category:

category	percentage
A	62.11%
B	62.11%
C	62.11%

I also created a 'Many to one (*:1)' relationship (Single direction) between the new table and my previous table for [category].

Any help would be greatly appreciated! Thank you

[amitchandak · Accepted solution]

@DavidJ , Create a measure and use that in visual

Measure = average(Table[daily percentage])

Or new table

table =

Summarize(Table, Table[Category], "Percent", average(Table[daily percentage])  )

[DavidJ]

Hi @amitchandak - thank you so much! I was just overcomplicating it.

The below worked perfectly. I didn't realise I didn't have to use the SELECTCOLUMNS expression when creating a new table (which I was previous wrapping in the SUMMARIZE expression).

summary table = 
SUMMARIZE(
    'table 1',
    'table 1'[category],
    "summary percentage",
    AVERAGE('table 1'[daily percentage])
)