Calculate Sum based on Column Status and Cross Referencing Tables by EmployeeID

[PerryJL]

I have a slicer on my page with the DisplayName showing the agents in my company from Table 2. In Table 1, we use the ReferenceID to track which agent put the entry into the system. These tables have the ReferenceID correctly linked in the relationships. I also have a calculated column to show what status the deal is in so I know if it should be included in the goals calculation or not. 

When I create a table in the report and add the display name, it links correctly to the slicer, however, when I add the GoalsTotal to the table, it just puts the same garbage sum of the column instead of the total for each agent. 

I'm a bit fried at this point so taking a break, but any help greatly appreciated as to what I may be doing wrong.

Table 1

colReferenceID, colFee, colStatus, GoalsTotal

xxx-xxx-xxx, 500, 5, [if colStatus = 5, colfee, 0]

yyy-yyy-yyy, 500, 2, [if colStatus = 5, colfee, 0]

Table 2

colReferenceID, colDisplayName

xxx-xxx-xxx, John Doe

yyy-yyy-yyy, Jane Smith

[PerryJL]

Not sure what happened here. I went through and re-imported all the data, taking care of how it was transformed, and essentially copy and pasted the formulas I had and it all works as expected. I am guessing there is a flaw in the first attempt dataset that is causing the issue.

[v-danhe-msft]

Hi @PerryJL,

Based on my test, it could work on my side:

Could you please offer your sample file to have a test if possible?

Regards,

Daniel He

[PerryJL]

Daniel,

While anonymizing the data to share with you, I reimported a subset of the data and reduced the data set, copied and pasted all the same calculated column information...and it worked as expected. I'm going to dig back through the original data set and see what might be causing the issue.

[v-danhe-msft · Accepted solution]

Hi @PerryJL,

Ok. It's pleasant that if your problem could be solved, you could mark the reply as Answered to close this topic and if you have any questions you can contact me.

Regards,

Daniel He