Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
crln-blue
Post Patron
Post Patron

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

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:

OpcoDateCategoryLocationLocation 2(many other fields)Value
AA10/19/2023Furniture406A 1
AA08/19/2023Chair408B 5
AA08/02/2023Chair406A 7
BB10/11/2023Comb410A 9
BB02/19/2023Furniture406A 5
BB02/19/2023Furniture408A 4
BB08/19/2023Chair404B 6
CC10/19/2023Chair406B 8
CC02/19/2023Chair410C 1
CC10/19/2023Furniture408D 5


My target output is this:

crlnblue_0-1697691688100.png

 

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.

 

 

 

4 REPLIES 4
mickey64
Super User
Super User

For your reference.

 

Step 1: I make a 'Table' visual.

    mickey64_0-1697694872565.png

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

mickey64_2-1697694973725.png

 

    mickey64_1-1697694931762.png

 

 

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
Super User
Super User

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.

 

Jihwan_Kim_1-1697694403985.png

 

 

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"}
    )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Ahmedx
Super User
Super User

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"})

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.