Forum Discussion
Merge rows by column value depending on date filter
I think I might have solved problem (2) by removing all aggregations except "Full data" = All rows, and also by setting everything (except Hours) to "Don't summarize".
On the other hand, I still see some multiple rows that are not supposed to be there. I have at least figured out that what causes this problem is adding a new column to do some calculations.
Basically, in addition to the Hours field I also have fields like Revenue and Cost. I need to show a column that calculates a profit margin, so I defined the column as this:
Profit Margin = ('Table'[Revenue] - 'Table'[Cost]) / 'Table'[Revenue]
When I show the calculated Profit Margin, the rows split up again. I think the reason is, that the Profit Margin is different for each row, because the calculation is actually done row-by row... It took me a while to figure it out, because it most cases the Profit Margin is actually identical when rounded up to the number of displayed digits! So for example I had multiple rows with Profit Margin = 0.75, but the truth is that one row might have been 0.751234 and the next 0.751235.
!!! I think the point of the problem is that I should not calculate the Profit Margin row-by-row, but instead calculate it using the total Revenue and total Cost for all rows that are previously grouped together.
Can anyone help me figure out how to do this? It could be the last missing piece of the puzzle to make this report work.
EDIT: BINGO! :) The solution was NOT to calculate the Profit Margin as a column but rather as a measure: Profit Margin = DIVIDE(SUM('Table'[Revenue]) - SUM('Table'[Cost]), SUM('Table'[Revenue]))
I am posting an update because eventually I manage to solve all my problems.
I figured out that the remaining problem (1) was because of an additional column in the table that I wanted to have a filter for, say a "Project" column.
Apparently, if I SHOW that column in the report, the table will show one separate row for each different value of this Project, and each one of these rows is the result of aggregating all the original rows. If I instead don't show that column in the table, all original rows are merged into one.
For example, with the original data being this:
ID Employee Hours Date Project --------------------------------------------- 1 John 2 1.1.2018 Alpha 2 Mary 5 12.1.2018 Alpha 3 Sue 4 4.2.2018 Alpha 4 Mary 3 20.2.2018 Alpha 5 John 1 25.2.2018 Beta 6 Bob 6 3.3.2018 Beta
the report table after merging would look like the following when the Project column is shown:
Employee Hours Project --------------------------------------------- John 2 Alpha Mary 8 Alpha Sue 4 Alpha John 1 Beta Bob 6 Beta
or it would look like the following when the Project column is hidden:
Employee Hours
------------------- John 3 Mary 8 Sue 4 Bob 6
It sounds to me like this is the intended behaviour, however it does get a little bit in the way... I think there is a way to still show ONE row per Employee even when there are multiple Project values, and then show something like this:
Employee Hours Project --------------------------------------------- John 3 Alpha, Beta Mary 8 Alpha Sue 4 Alpha Bob 6 Beta