Creating DAX table - getting the average based on the columns in the same dax table formula

[crln-blue]

Hello!

I'm trying to create a dax table so I can summarize my data and use it for the visuals (building a measure was not an option huhu because I need legends..). 

Along with the dimension fields that I chose is that I want to have an average of the values based on that fields in the dax table.

Here is my sample data:

Opco	Date	Category	Location	Location 2	(many other fields)	Value
AA	10/19/2023	Furniture	406	A		1
AA	08/19/2023	Chair	408	B		5
AA	08/02/2023	Chair	406	A		7
BB	10/11/2023	Comb	410	A		9
BB	02/19/2023	Furniture	406	A		5
BB	02/19/2023	Furniture	408	A		4
BB	08/19/2023	Chair	404	B		6
CC	10/19/2023	Chair	406	B		8
CC	02/19/2023	Chair	410	C		1
CC	10/19/2023	Furniture	408	D		5

My target output is this:

The Average column is also dependent on the Opco, Month-Year and Category.

Below is my dax table code:

CALCULATETABLE(
    SUMMARIZE(
        ADDCOLUMNS(
            ADDCOLUMNS(
                'main',
                    "Opco", 'main'[Opco],
                    "Date",  CONCATENATE(''main''[date].[Month], CONCATENATE(" ", 'main'[date].[Year])),
                    "Category", 'main'[Category]
                ),
           "Average Values", CALCULATE(AVERAGE('main'[Value]),
            "Opco" = 'main'[Opco], 
            "Date"= 'main'[Date],
            "Category" = 'main'[Category]
            )
        ),
        [Opco],
        [Date],
        [Category],
        [Average Values]
    ),FILTER('main', 'main'[Category] = "Furniture" || 'main'[Category] = "Chair")
)

It's not working and I think it's because I wasnt ableto evaluate the filter expression for the Average. However, I'm not sure how since I'm already computing the average in the same dax formula. And I need it to be in the dax table formula too because I'm gonna use it to union with another table..
What would be the correct way?
Thank you very much.

[mickey64]

For your reference.

Step 1: I make a 'Table' visual.

    

*** I should change 'Sum of Value' to 'Average of Value'. ***

    

[crln-blue]

Hello, thank you for the help! I understand that I can drag my column and select Average in the aggregation but I'm planning to have a separate Average column so I can union it with the values of another table.. But thank you for the help! ^_^

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = 
VAR _addmonthyear =
    ADDCOLUMNS ( main, "@monthyear", FORMAT ( main[Date], "mmmm yyyy" ) )
VAR _groupbyaverage =
    GROUPBY (
        _addmonthyear,
        main[Opco],
        [@monthyear],
        main[Category],
        "@average", AVERAGEX ( CURRENTGROUP (), main[Value] )
    )
RETURN
FILTER(
    _groupbyaverage, main[Category] in {"Furniture", "Chair"}
    )

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RizLYtaAN2ydmhjj3?e=FC0CAT

New table = FILTER(SUMMARIZE(
    'Table',
    'Table'[Opco],
    'Table'[Category],
    Calendar[Year Month],
    "Sales", DIVIDE( [Amo], COUNTROWS( 'Table' ) )
),'Table'[Category] in {"Chair", "Furniture"})