Forum Discussion
Using DAX to Create a New Column Based on Groups Within Table
Hello,
I am trying to write a DAX function on a calculated table to create a new column in that table that is based on a grouping of data in multiple other columns in the same table.
For example, I have a hypothetical table named "Sales_Table_2022" organized as follows, with the blank column I am trying to create:
| Sales_Team | Sales_Location | Month | Sales | Max Sales for Group |
| A | Domestic | 1 | $10,000 | |
| A | Domestic | 2 | $7,000 | |
| A | Foreign | 1 | $8,000 | |
| A | Foreign | 2 | $11,000 | |
| B | Domestic | 1 | $5,000 | |
| B | Domestic | 2 | $6,000 | |
| B | Foreign | 1 | $4,000 | |
| B | Foreign | 2 | $3,000 |
Where the column I'm trying to create reports the maximum sales for each unique combination of Sales_Team and Sales_Location. I therefore want to group by team and location, producing a calculation within the table. The end result would look like this:
| Sales_Team | Sales_Location | Month | Sales | Max Sales for Group |
| A | Domestic | 1 | $10,000 | $10,000 |
| A | Domestic | 2 | $7,000 | $10,000 |
| A | Foreign | 1 | $8,000 | $11,000 |
| A | Foreign | 2 | $11,000 | $11,000 |
| B | Domestic | 1 | $5,000 | $6,000 |
| B | Domestic | 2 | $6,000 | $6,000 |
| B | Foreign | 1 | $4,000 | $4,000 |
| B | Foreign | 2 | $3,000 | $4,000 |
I get the sense that I will need a DAX function that proposes a table within the function and then evaluates the table to produce scalar values to fit in one column.
Please help! I would greatly appreciate support here, especially with general guidance on DAX functions that propose a table but then produce a scalar output.
Best,
Nate
Try this: (Sales_MaxPerGroup is the table name)
Sales max per Sales_Location = CALCULATE( Max(Sales_MaxPerGroup[Sales]), ALLEXCEPT(Sales_MaxPerGroup, Sales_MaxPerGroup[Sales_Team], Sales_MaxPerGroup[Sales_Location]))
8 Replies
Try this: (Sales_MaxPerGroup is the table name)
Sales max per Sales_Location = CALCULATE( Max(Sales_MaxPerGroup[Sales]), ALLEXCEPT(Sales_MaxPerGroup, Sales_MaxPerGroup[Sales_Team], Sales_MaxPerGroup[Sales_Location]))This solution works and seems to be very efficient and easy to manage! I looked at how the Microsoft Learn website describes ALLEXCEPT and still don't really get how it works. Does it group rows based on the columns identified in the function? Could you point me to resources for understanding "context" filters?
I agree Microsoft documentation is not for all users. I too had hardship in reading and understanding many times.
ALLEXCEPT:
https://learn.microsoft.com/en-us/dax/allexcept-function-dax
Other documentation links that can explain:
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
ALLEXCEPT is like give me all table rows and apply filters only to these columns.
M1 = CALCULATE ( SUM (Table[Value1]), ALLEXCEPT(Table, Table[Column1], Table[Column2]) )Say, in SQL, it is like
Note: 1. If there is active filter then you apply the filter for the columns provided. If there is no active filter then NO WHERE clause for the columns provided. 2. For the remaining columns DO NOT apply any filter, whether filters exist or not. 3. Say, if you have active filters for Column1, Column2 then it will be like ... SELECT SUM( Table.Value1) FROM Table WHERE Column1 IN (....) AND Column2 IN (....) 4. Say, if you have active filters only for Column2 and no filters for Column1 then it will be like ... SELECT SUM( Table.Value1) FROM Table WHERE Column2 IN (....)
Hi , Anonymous
According to your description, you want to add a calculated column in the table Based on Groups Within Table.
Here are the steps you can refer to :
(1)My test data is the same as yours.
(2)We can click "New Column" and enter this , you need to update the table name in your side .Column = var _t = FILTER( 'Table', 'Table'[Sales_Team] =EARLIER('Table'[Sales_Team]) && 'Table'[Sales_Location] = EARLIER('Table'[Sales_Location])) return MAXX(_t,[Sales])(3)Then we can meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi,
It will be much easier to write a calculated column formula. Are you amenable to that idea?
Yes, I would be amenable to a calculated column. I've been curious about using GROUPBY or SUMMARIZE to produce a new column, rather than a new table. A new smaller table formed through these tables is not helpful here because I cannot Manage Relationship by linking the tables based on two or more variables.
Hi,
This calculated column formula works
Column = CALCULATE(MAX(Data[Sales]),FILTER(Data,Data[Sales_Team]=EARLIER(Data[Sales_Team])&&Data[Sales_Location]=EARLIER(Data[Sales_Location])))Hope this helps.
