Forum Discussion
How to achieve this: Multi-Rows with same unique ID
Hi
I've two data sources. Each data source has similar as well as different columns. Below is an extract of all columns.
The first table contains all transactions (TX Table)
| Ref# | Date | Quarter | Month | Revenue ($) |
| 30243763-140 | 2021-10-06 | 2022-Q1 | 2022-M01 | $21,896 |
| 30243763-140 | 2021-11-05 | 2022-Q1 | 2022-M02 | -$51,227 |
| 30243666-100 | 2021-10-06 | 2022-Q1 | 2022-M01 | $15,896 |
| 30243666-100 | 2021-11-05 | 2022-Q1 | 2022-M02 | -$5,122 |
| 30987666-240 | 2021-10-06 | 2022-Q1 | 2022-M01 | $32,100 |
| 30987666-240 | 2021-11-05 | 2022-Q1 | 2022-M02 | -$1,227 |
| 30243789-90 | 2021-10-06 | 2022-Q1 | 2022-M01 | $8,348 |
| 30243789-90 | 2021-11-05 | 2022-Q1 | 2022-M02 | -$2,500 |
| 39243763-40 | 2021-10-06 | 2022-Q1 | 2022-M01 | $7,800 |
| 38243688-10 | 2021-11-05 | 2022-Q1 | 2022-M02 | $4,500 |
| 37987546-60 | 2021-10-06 | 2022-Q1 | 2022-M01 | $3,200 |
| 36243789-10 | 2021-11-05 | 2022-Q1 | 2022-M02 | $6,800 |
The second table, contains transactions that are eligible for bonus (Bonus Table)
| Ref# | Quarter | Month | Eligible amount for Bonus ($) |
| 30243763-140 | 2022-Q1 | 2022-M03 | $14,600.00 |
| 30243666-100 | 2022-Q1 | 2022-M01 | $10,500.00 |
| 30987666-240 | 2022-Q1 | 2022-M01 | $30,250.00 |
| 30243789-90 | 2022-Q1 | 2022-M01 | $6,200.00 |
| 30243789-90 | 2022-Q1 | 2022-M02 | $6,000.00 |
| 39243763-40 | 2022-Q1 | 2022-M01 | $4,000.00 |
| 38243688-10 | 2022-Q1 | 2022-M02 | $4,200.00 |
| 37987546-60 | 2022-Q1 | 2022-M01 | $2,800.00 |
| 36243789-10 | 2022-Q1 | 2022-M02 | $6,300.00 |
What I would like to see in my visualization is the first 4 columns of TX table + Last column of Bonus Table showing similar to this:
| Ref# | Date | Quarter | Month | Eligible amount for Bonus ($) |
| 30243763-140 | 2021-10-06 | 2022-Q1 | 2022-M01 | $14,600.00 |
| 30243763-140 | 2021-11-05 | 2022-Q1 | 2022-M02 | $0.00 |
| 30243666-100 | 2021-10-06 | 2022-Q1 | 2022-M01 | $10,500.00 |
| 30243666-100 | 2021-11-05 | 2022-Q1 | 2022-M02 | $0.00 |
| 30987666-240 | 2021-10-06 | 2022-Q1 | 2022-M01 | $30,250.00 |
| 30987666-240 | 2021-11-05 | 2022-Q1 | 2022-M02 | $0.00 |
| 30243789-90 | 2021-10-06 | 2022-Q1 | 2022-M01 | $6,200.00 |
| 30243789-90 | 2021-11-05 | 2022-Q1 | 2022-M02 | $6,000.00 |
| 39243763-40 | 2021-10-06 | 2022-Q1 | 2022-M01 | $4,000.00 |
| 38243688-10 | 2021-11-05 | 2022-Q1 | 2022-M02 | $4,200.00 |
| 37987546-60 | 2021-10-06 | 2022-Q1 | 2022-M01 | $2,800.00 |
| 36243789-10 | 2021-11-05 | 2022-Q1 | 2022-M02 | $6,300.00 |
NOTE: In TX Table, Ref# value can repeat. But in Bonus table, it doesn't repeat.
So, what I want is the first occuanrace of the Ref# in the new table to have the value of Eligible Amount for Bonus and the second occurance to have 0.
I tried to use SummarizeColumn but this would repeat the same value for each line for "Eligible amount for Bonus ($)" (when Ref# is repeated).
Anythoughts?
First, some inconsistencies that may need clarification.
- You do actually have repeat refs in the provided example bonus table (e.g. 30243789-90).
- Your output table seems to be inconsistently pushing bonuses to first date (e.g. pushing M03 bonus to M01 for 30243763-140, but keeping M02 bonus in M02 row for 30243789-90). Is the output table incorrect, or does more of the logic need to be articulated on why we are summarizing at earliest date row for some refs but not others?
With that said, I came up with two measures (for w/o totals and w/ totals) that should work assuming the following:
- You want everything summarized on every Ref#'s earliest date row
- There is no relationship set up between the tables.
Note, if repeat Ref# are in both tables, to set up a relationship, we would either need to create a relationship on a calculated column added to each table that factors in Ref# + Date or Month, M:M relationship (with I assume filtering direction from TX Table --> Bonus Table), or a dinstict Ref# table in between the two so we get M:1:M relationship
I've provided two measures, almost identical, except one has an outside SUMX to enable subtotals/totals:
Eligible amount for Bonus ($) = VAR CurDate = SELECTEDVALUE( 'TX Table'[Date] ) VAR CurRef = SELECTEDVALUE( 'TX Table'[Ref#] ) VAR RefRankTable = CALCULATETABLE( VALUES( 'TX Table'[Date] ), REMOVEFILTERS('TX Table' ), VALUES('TX Table'[Ref#] ) ) VAR RefRank = RANKX( RefRankTable, 'TX Table'[Date], CurDate, ASC ) VAR Amount = IF( RefRank = 1, CALCULATE( SUM('Bonus Table'[Eligible amount for Bonus ($)]), TREATAS( { CurRef },'Bonus Table'[Ref#] ) ), 0 ) RETURN Amount Eligible amount for Bonus ($) w/ Total = SUMX( 'TX Table', VAR CurDate = 'TX Table'[Date] VAR CurRef = 'TX Table'[Ref#] VAR RefRankTable = CALCULATETABLE( VALUES( 'TX Table'[Date] ), REMOVEFILTERS('TX Table' ), VALUES('TX Table'[Ref#] ) ) VAR RefRank = RANKX( RefRankTable, 'TX Table'[Date], CurDate, ASC ) VAR Amount = IF( RefRank = 1, CALCULATE( SUM('Bonus Table'[Eligible amount for Bonus ($)]), TREATAS( { CurRef },'Bonus Table'[Ref#] ) ), 0 ) RETURN Amount )Output:
4 Replies
- MarkLafSuper User
First, some inconsistencies that may need clarification.
- You do actually have repeat refs in the provided example bonus table (e.g. 30243789-90).
- Your output table seems to be inconsistently pushing bonuses to first date (e.g. pushing M03 bonus to M01 for 30243763-140, but keeping M02 bonus in M02 row for 30243789-90). Is the output table incorrect, or does more of the logic need to be articulated on why we are summarizing at earliest date row for some refs but not others?
With that said, I came up with two measures (for w/o totals and w/ totals) that should work assuming the following:
- You want everything summarized on every Ref#'s earliest date row
- There is no relationship set up between the tables.
Note, if repeat Ref# are in both tables, to set up a relationship, we would either need to create a relationship on a calculated column added to each table that factors in Ref# + Date or Month, M:M relationship (with I assume filtering direction from TX Table --> Bonus Table), or a dinstict Ref# table in between the two so we get M:1:M relationship
I've provided two measures, almost identical, except one has an outside SUMX to enable subtotals/totals:
Eligible amount for Bonus ($) = VAR CurDate = SELECTEDVALUE( 'TX Table'[Date] ) VAR CurRef = SELECTEDVALUE( 'TX Table'[Ref#] ) VAR RefRankTable = CALCULATETABLE( VALUES( 'TX Table'[Date] ), REMOVEFILTERS('TX Table' ), VALUES('TX Table'[Ref#] ) ) VAR RefRank = RANKX( RefRankTable, 'TX Table'[Date], CurDate, ASC ) VAR Amount = IF( RefRank = 1, CALCULATE( SUM('Bonus Table'[Eligible amount for Bonus ($)]), TREATAS( { CurRef },'Bonus Table'[Ref#] ) ), 0 ) RETURN Amount Eligible amount for Bonus ($) w/ Total = SUMX( 'TX Table', VAR CurDate = 'TX Table'[Date] VAR CurRef = 'TX Table'[Ref#] VAR RefRankTable = CALCULATETABLE( VALUES( 'TX Table'[Date] ), REMOVEFILTERS('TX Table' ), VALUES('TX Table'[Ref#] ) ) VAR RefRank = RANKX( RefRankTable, 'TX Table'[Date], CurDate, ASC ) VAR Amount = IF( RefRank = 1, CALCULATE( SUM('Bonus Table'[Eligible amount for Bonus ($)]), TREATAS( { CurRef },'Bonus Table'[Ref#] ) ), 0 ) RETURN Amount )Output:
- AnonymousNot applicable
Thanks MarkLaf
The second Measure worked perfectly fine. Only the total is not showing properly.
But the logic is what I asked for.
- MarkLafSuper User
Thanks for pointing out the issue with the total. When Ref# wasn't filtered, it was only summing bonuses for earliest (tied) date for all the selected Ref#'s. This measure is working better with totals, at least with the sample data provided:
Eligible amount for Bonus ($) w/ Total_fixedtotals = VAR RefTab = GENERATE( SUMMARIZE( 'TX Table' ,'TX Table'[Ref#],'TX Table'[Month]), VAR RefRankTable = CALCULATETABLE( VALUES( 'TX Table'[Month] ), REMOVEFILTERS('TX Table' ), TREATAS( { 'TX Table'[Ref#] },'TX Table'[Ref#] ) ) VAR RefRank = RANKX( RefRankTable, 'TX Table'[Month], , ASC ) RETURN ROW( "Rank", RefRank) ) RETURN CALCULATE( SUM('Bonus Table'[Eligible amount for Bonus ($)]) + 0, TREATAS( CALCULATETABLE( VALUES('TX Table'[Ref#] ), FILTER( RefTab, [Rank] = 1 ) ), 'Bonus Table'[Ref#] ) )