Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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#DateQuarterMonthRevenue ($)
30243763-1402021-10-062022-Q12022-M01$21,896
30243763-1402021-11-052022-Q12022-M02-$51,227
30243666-1002021-10-062022-Q12022-M01$15,896
30243666-1002021-11-052022-Q12022-M02-$5,122
30987666-2402021-10-062022-Q12022-M01$32,100
30987666-2402021-11-052022-Q12022-M02-$1,227
30243789-902021-10-062022-Q12022-M01$8,348
30243789-902021-11-052022-Q12022-M02-$2,500
39243763-402021-10-062022-Q12022-M01$7,800
38243688-102021-11-052022-Q12022-M02$4,500
37987546-602021-10-062022-Q12022-M01$3,200
36243789-102021-11-052022-Q12022-M02$6,800

 

The second table, contains transactions that are eligible for bonus (Bonus Table)

Ref#QuarterMonthEligible amount for Bonus ($)
30243763-1402022-Q12022-M03$14,600.00
30243666-1002022-Q12022-M01$10,500.00
30987666-2402022-Q12022-M01$30,250.00
30243789-902022-Q12022-M01$6,200.00
30243789-902022-Q12022-M02$6,000.00
39243763-402022-Q12022-M01$4,000.00
38243688-102022-Q12022-M02$4,200.00
37987546-602022-Q12022-M01$2,800.00
36243789-102022-Q12022-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#DateQuarterMonthEligible amount for Bonus ($)
30243763-1402021-10-062022-Q12022-M01$14,600.00
30243763-1402021-11-052022-Q12022-M02$0.00
30243666-1002021-10-062022-Q12022-M01$10,500.00
30243666-1002021-11-052022-Q12022-M02$0.00
30987666-2402021-10-062022-Q12022-M01$30,250.00
30987666-2402021-11-052022-Q12022-M02$0.00
30243789-902021-10-062022-Q12022-M01$6,200.00
30243789-902021-11-052022-Q12022-M02$6,000.00
39243763-402021-10-062022-Q12022-M01$4,000.00
38243688-102021-11-052022-Q12022-M02$4,200.00
37987546-602021-10-062022-Q12022-M01$2,800.00
36243789-102021-11-052022-Q12022-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.

    1. You do actually have repeat refs in the provided example bonus table (e.g. 30243789-90). 
    2. 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:

    1. You want everything summarized on every Ref#'s earliest date row
    2. 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

  • First, some inconsistencies that may need clarification.

    1. You do actually have repeat refs in the provided example bonus table (e.g. 30243789-90). 
    2. 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:

    1. You want everything summarized on every Ref#'s earliest date row
    2. 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:

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks MarkLaf 

      The second Measure worked perfectly fine. Only the total is not showing properly.

      But the logic is what I asked for.

       

       

      • MarkLaf's avatar
        MarkLaf
        Super 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#] 
            ) 
        )