Forum Discussion
Post PatronCreating 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:
| 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.
4 Replies
- Ahmedx
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"}) - 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"} ) - mickey64
For your reference.
Step 1: I make a 'Table' visual.
*** I should change 'Sum of Value' to 'Average of Value'. ***
crln-blueHello, 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! ^_^
