Calculate difference between indirectly related tables

[sherryliu]

Hi 

I am working on a BI report on HR time attendence vs CRM time spending. I had a common employee and calendar table between HR TLM table and CRM table. Is it possible to calculate the differences between HR time and CRM time based on date and employee? I had a gauge visual that displays HR time as Target and CRM time as fill. The goal is to detect any false reporting under CRM. CRM time should equal or less than HR time.  If I have the diffferences between HR and CRM time, I can direct managers to the employees who had incorrect time & attendance record. Thanks a lot for you help.

[JoeBarry · Accepted solution]

Hi @sherryliu 

It seems that the relationships are set up correctly. Are the time columns in both tables in Integer format? if they are try this

Total HR Time = SUM(HR[Time])

Total CRM Time = SUM(CRM[Time])

You can then create a thrird measure to check the difference between both calculations.

Diff = [Total CRM Time] - [Total HR Time]

In a matrix add the date and then the employee name in the row section as there could be a lot of data i wouldn't add any of these in the Column section.

Add the measure in the value section,

In the format pane go to Cell elements > Apply settings to Activate background colur and click on fx

in the Format Style choose "Rules"

In the "What field should we base this on?" Select the Diff measure

Then you need to add <= 0 and > lets say - 500(it won't be that large, but it doesn#t amke a difference) Then give it the colur green

Add a new rule > 0 and <= 500 and then make this colour Red.  These fields should be number and not percentage.

If you don't want a green colour, just use the red condition. 

Thanks

Joe

If this post helps, then please Accept it as the solution

[technolog]

Can you please tell me if the problem is still relevant? If you managed to solve it in some way, please write here how you solved this problem. Then it will be possible to mark this problem as solved.

If not, it may make sense to ask the question again, thus confirming the relevance of this problem.