Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi! I am a big fan of this community! 🤗 I search all my Power BI questions in here and always have an answer from previusly asked questions. This time I am posting my own question because unfortunately couldn't find similar post. Appriciate a lot if you could help 🙂 🙂 🙂 I am really really stuck.
I have EEs table with thousands of EEs for each period with below data:
Period | EE# | Profit Center | Grade |
2020Q1 | 1 | 22 | 3 |
2020Q1 | 2 | 44 | 5 |
2020Q2 | 1 | 22 | 3 |
2020Q2 | 2 | 33 | 5 |
2020Q2 | 3 | 44 | 7 |
I also have a table with all EEs salary components:
Period | EE# | Category | USD |
2020Q1 | 1 | Base Salary | 20000 |
2020Q1 | 1 | Bonuses | 5000 |
2020Q1 | 2 | Base Salary | 40000 |
2020Q1 | 2 | Bonuses | 4000 |
2020Q1 | 2 | Leasing | 500 |
I have a one to many connection between the 2 tables using combined column of period & EE#.
I now need to calculate average salary for every period which will be dinamic and I will be ably to slice/filter it by profit center and other parameters. So I used DIVIDE() measure, but my problem is when I present it in stacked collumn chart with Category as legend - I notice that the average salary is calculated separatly for each Category using total EEs in each caltegory - in the example 2 EEs in Base Salary, 2 in Bonuses and 1 in Leasing. I would like to have same total of EEs in every category - which is total EEs I have in each period - 2 in Q1, 3 in Q2. Is it possible? I tried ALL, ALLSELECTED, ALLEXCEPT.
Appriciate a lot for any direction!!!
Thank you
Natalie
Solved! Go to Solution.
@NatalieTlash I'm not 100% what you want. Try making your relationship bi-directional? What is your measure formula and what is your expected output?
Thank you Greg! You are right, the issue was the relationship between the tables, it was bi directional and should be one direction! You are such a pro 😃
@NatalieTlash I'm not 100% what you want. Try making your relationship bi-directional? What is your measure formula and what is your expected output?
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |