Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
First, some inconsistencies that may need clarification.
With that said, I came up with two measures (for w/o totals and w/ totals) that should work assuming the following:
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:
First, some inconsistencies that may need clarification.
With that said, I came up with two measures (for w/o totals and w/ totals) that should work assuming the following:
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:
Thanks @MarkLaf
The second Measure worked perfectly fine. Only the total is not showing properly.
But the logic is what I asked for.
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#]
)
)
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |