Obtaining accurate totals in Matrix visual

[jburklund]

We are trying to solve for individual paycheck amounts given the YTD pay amount (ytd_wages_subject_to_FUTA) in the Paycheck Trial measure. We then need to adjust the granularity up a level to sum all of the paycheck amounts for a given time period, in this case FY 2020 from January - December. We cannot rely on the YTD wages column because sometimes the FYE does not fall on the calendar year end or we would like to aggregate on the employee's address rather than each individual pay date.

We are experiencing the problem outlined in this post from SQLBI but have not been able to successfully adjust our DAX measure to solve our problem:  https://www.sqlbi.com/articles/obtaining-accurate-totals-in-dax/

We need the Paycheck Trial measure to provide an accurate sum of all of the individual paycheck amounts in the subtotal for the Employee on the matrix visual. For example, Oscar the Grouch is showing a sum of $2,520,100 for FY 2020 when it should total all of the individual check amounts in the Paycheck Trial column totaling $100,600.

We included sample data for 3 employees for FY 2020 with December paychecks from 2019 as well to test if we can adjust the time frame and still come up with an accurate summation at the matrix level of wages per employee.

Link to GoogleDrive with sample PBIX file: https://drive.google.com/drive/folders/12S4mvom2rKnWBXp6PGFnP5gDY3WOlJBZ?usp=sharing

[v-yuaj-msft · Accepted solution]

Hi @jburklund ,

Sorry for late reply. It's indeed a little complicated. I took a few time for test.

You can do some steps as follows.

1. create a calculated column.

Rank = RANKX(FILTER(ALL('vw_payroll_1'),[social_security_number]=EARLIER('vw_payroll_1'[social_security_number])),CALCULATE(VALUES(vw_payroll_1[last_check_date])),,ASC)

2. create two measures.

Measure = 

IF(

MAX('vw_payroll_1'[Rank])=MINX(

 FILTER(

 ALLSELECTED('vw_payroll_1'),

 [social_security_number]=MAX('vw_payroll_1'[social_security_number])),

 [Rank]

 ),

SUM('vw_payroll_1'[ ytd_wages_subject_to_FUTA ]),

SUM('vw_payroll_1'[ ytd_wages_subject_to_FUTA ])-SUMX(

 FILTER(

 ALLSELECTED('vw_payroll_1'),

 [social_security_number]=MAX('vw_payroll_1'[social_security_number])&&[Rank]=MAX('vw_payroll_1'[Rank])-1),

 [ ytd_wages_subject_to_FUTA ])

 )

Test = 

var x1=SUMMARIZE('vw_payroll_1',vw_payroll_1[social_security_number],vw_payroll_1[last_check_date],vw_payroll_1[ ytd_wages_subject_to_FUTA ],"mease",[Measure])

var x2=SUMX(x1,[mease])

return

IF(ISINSCOPE(vw_payroll_1[social_security_number]),IF(ISINSCOPE('vw_payroll_1'[last_check_date]),[Measure],x2),x2)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yuaj-msft]

Hi @jburklund ,

Thanks for sharing your example data. But maybe I'm not getting what actually you are trying to do. Do you want to replace the value of"Paycheck Trial" measure with the value of "Max of ytd_wages_subject_to_FUTA " in the following table?

Could you please share your expected result with me ? (a screenshot or a table as well)

Best Regards,

Yuna

[jburklund]

Hi @v-yuaj-msft ,

Attached is a screenshot and I also uploaded the sample data and needed solution as an excel file in the GoogleDrive folder linked above.

The Paycheck Trial measure is correctly solving for the current paycheck amount given the YTD wages in  the "ytd_wages_subject_to_FUTA" column. However, it is incorrectly summing on the Matrix at the level of the employee and not just the individual paycheck lines. $2,520,100 for Oscar the Grouch should be summing each paycheck amount and total $100,600.

[jburklund]

Updated link to GoogleDrive