Forum Discussion
How to use SUMMARIZE inside a calculated column?
I'm trying to get information from aggregated version of my table into my original table as a column, but im not sure how to do it.
Find the sample ecxample below
The table contains 4 column - EMP_ID, DATE, MONTHLY SALARY, DESIGNATION
I want to create new column called TOTAL SALARY which is the sum of salary for each employee available in data.
There is time filter as well, like if I select 6 months in the filter visual the total salary should be populated as total six months salary.
I couldn't find a way to do it in PBI using DAX/POWER QUERY.
Please help me on this!!
Thanks
aji549 Not sure I am following this. Calculated columns cannot be affected by filters so you would need a measure in that case. As a measure, you could potentially do something like this:
Measure = VAR __Emp = MAX('Table'[EMP_ID]) VAR __Result = SUMX( FILTER( ALLSELECTED( 'Table' ), [EMP_ID} = __Emp ), [SALARY] ) RETURN __ResultHi, aji549
You can refer to Greg_Deckler reply. If it doesn't meet your needs, you can refer to the following DAX.Total SALARY = CALCULATE( SUM('Table'[SALARY]), FILTER( 'Table', 'Table'[EMP_ID] = EARLIER('Table'[EMP_ID]) ) )Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
2 Replies
aji549 Not sure I am following this. Calculated columns cannot be affected by filters so you would need a measure in that case. As a measure, you could potentially do something like this:
Measure = VAR __Emp = MAX('Table'[EMP_ID]) VAR __Result = SUMX( FILTER( ALLSELECTED( 'Table' ), [EMP_ID} = __Emp ), [SALARY] ) RETURN __ResultHi, aji549
You can refer to Greg_Deckler reply. If it doesn't meet your needs, you can refer to the following DAX.Total SALARY = CALCULATE( SUM('Table'[SALARY]), FILTER( 'Table', 'Table'[EMP_ID] = EARLIER('Table'[EMP_ID]) ) )Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum