Forum Discussion
Advocate ICalculate difference between indirectly related tables
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.
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
2 Replies
- technolog
Super User
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.
- JoeBarry
Solution Sage
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
