So if I removed/unfiltered Department, the % is based on total of all employee and not the selected department. So I believe the department should be filtered. The grade should not be filtered for the selected department. I can figured out how to write the formula when two different dimensions/tables are involved. My department is from DIM_OBS table (my slicer). My grade is from my Fact_Gender_Race table.
Thank you for helping!
Please bear in mind that in a proper design, which also makes it easy to create correct formulas, you should have a star schema - all your dimensions should hold attributes that you slice by and the fact table should ONLY hold your figures and keys to the dimensions to the point where you can hide all the columns in your fact table. Currently, your design is not proper since your fact table does contain something that you slice by. If you want to be on the safe side and avoid headaches in the future, please turn your model into a PROPER ONE. One more thing to say about the proper design is that if you keep all attributes in your dimensions, you'll be able to see all of the possible values, whereas if you keep them in your fact table, this might not be the case - you'll only see the ones that do have some entries in the fact table. This might be misleading.
Hi Darek - So I made some adjustments and use grades from Dim_employee instead of from my fact table. The chart seems to be working. I also hide all unnecessary columns from my fact tables.