Forum Discussion
Merge rows by column value depending on date filter
Well I am getting closer and closer... but I am not quite there yet!
I have figured out how to convert the column types back after expanding the subtables. I don't think it is a good feature of PowerBI that it converted those automatically to string, and that I had to figure out how to convert them back manually, but nevertheless what matters is that now I can use some of the expanded columns for further calculations (which was impossible when they were strings) as well as use the expanded date in a date filter.
However, I noticed that there are still TWO huge problems with the grouping/merging itself: in the report, (1) some merging is incomplete so I still get multiple rows per employee, and (2) the aggregations don't work as expected by are multiplied by the number of original rows. In more details:
(1) So for example I might have 10 rows originally with the same employee, and in the QUERY EDITOR I can see they are correctly merged into 1 when I look at them in the query editor after the Grouped Rows step (and if I look at them after the Expanded Full data step, I can see again 10 rows). They also look as still separate rows in the DATA VIEW. However in the REPORT VIEW, they might show up incorrectly as something different e.g. as 2 rows, one corresponding to merging 7 original rows and another corresponding to the remaining 3.
(2) "Sum"-type and "Counter"-type aggregations in Group By are not really working as expected. For instance, I am including a "Total hours" column that is always supposed to show the sum of all hours done by each employee. Instead, the Total hours column shows the product between all hours and number of rows of that employee that are captured by the date filter. Similarly, as a test column I added a "Row counter" column, but instead of showing the total number of rows per employee, it shows the total number of rows per employee multiplied again per the number of rows captured by the filter!
To clarify, with regard to the sample tables in my first post, these are my EXPECTED RESULTS depending on the Date filter:
Employee Hours Total hours Row counter (Date filter from 1.1.2018 to 31.12.2018) --------------------------------------------- -> filter selects ALL original rows John 3 3 2 Mary 8 8 2 Sue 4 4 1 Bob 6 4 1
Employee Hours Total hours Row counter (Date filter from 1.1.2018 to 31.01.2018) --------------------------------------------- -> filter selects TWO original rows John 2 3 2 Mary 5 8 2
Employee Hours Total hours Row counter (Date filter from 1.1.2018 to 20.02.2018) --------------------------------------------- -> filter selects FOUR original rows John 2 3 2 Mary 5 8 2
Sue 4 4 1
Instead, these are the ACTUAL RESULTS (errors in bold):
Employee Hours Total hours Row counter (Date filter from 1.1.2018 to 31.12.2018) --------------------------------------------- -> filter selects ALL original rows John 3 6 4 Mary 8 16 4 Sue 4 4 1 Bob 6 4 1
Employee Hours Total hours Row counter (Date filter from 1.1.2018 to 31.01.2018) --------------------------------------------- -> filter selects TWO original rows John 2 3 2 Mary 5 8 2
Employee Hours Total hours Row counter (Date filter from 1.1.2018 to 20.02.2018) --------------------------------------------- -> filter selects FOUR original rows John 2 3 2 Mary 8 16 4
Sue 4 4 1
-------------------------------------------
And here's a summary of my current setup:
QUERY EDITOR
Source data includes ID, Employee, Hours, Date
Grouped Rows features "Group by" = Employee, and three aggregations: "Total hours" = Sum (Hours), "Full data" = All rows, "Row counter" = Count Rows
Expanded Data selects Date and Hours
Changed Type changes Date back to type->date, and Hours back to type->decimal
REPORT EDITOR
FIELDS selected for my Table visualization are Employee, Hours, Total hours, Full data.Hours, Row counter
Note that Full data.Date is NOT selected to be included in the Table. If I select also Full data.Date, the Table will show all the original rows (with no merging), because each row had different Date values. But because the Date is expanded (and converted), I can use it in a Filter on the report, and that seems to work fine (i.e. when I select a certain date range, only the original rows with matching Date values are included).
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 6It 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