Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All, I need some help ..
I have 3 tables where one is BILLING ,users list and team ,currentteam. with Employe and current dept .I wanted to calculate the sum for each YTD for EMPLOYEE ,by team .
1 Senario
if employee never change the team get sum (amt) irrrespective of the dept .
2.
If employee changed the teams from 11 to 22 and we select 22 which is currect team . then only display his current dept sum(amt) ie BBC 20 and If we select 11 then display sum(other amount) ie leave his current team dept amout . Ie
sum(amount) not inclusive of DEPT BBC
I am trying to create a measure for this . using calculate and filters .. but i am not sucessfull
1 Billing table
EMPLOYEE | period(year&month) | Dept | Amount |
1 | 201912 | ABC | 10 |
1 | 202001 | ABC | 12 |
1 | 202002 | BBC | 14 |
2 | 201912 | ABC | 10 |
2 | 202001 | ABC | 12 |
2 | 202002 | DBF | 14 |
2 | 202003 | BBC | 20 |
3 | 201912 | CDE | 19 |
2 | 202001 | BBC | 10 |
2 Employee ytd TABLE
EMPLOYEE | period(year&month) | team |
1 | 201912 | 11 |
1 | 202001 | 11 |
1 | 202002 | 11 |
1 | 202003 | 11 |
2 | 201912 | 11 |
2 | 202001 | 22 |
2 | 202002 | 22 |
3 | 201912 | 44 |
3 .Current user table latest month.
employee | dept |
1 | ABC |
2 | BBC |
3 | ABC |
You would require 1 calculated column to tag the latest department in the billing table and based on that you can create your measure.
CurrentUser Table
The first cust column help to define current dept .. if true or false
then there is a realtionship between teammaster and EmployeeYTD ,So when i select team 1 from the slicer then the count is always 1 for that team .. .. .
TEAM ID | DEPT |
1 | ABC |
2 | BBC |
So I tried something like below .. .. and got the current dept amount correctly .. but when i selected the second team I should get the remaining amount ..Since the first count condition is satisfied its is not going to else if i select the old team.
I am unable to understand your full requirements. Let me know what output are you expecting. TeamID comes from TeamMaster Table.
Attached screenshot of the output i get based on my TeamID Slicer.
If the employe doesnt change the team then we should get the total amount ignoring the dept he worked .
So for employee 1 amount is 36 -team 1
So for employee 2 - If we select team 1 (old team) then we need to get the sum(all dept except BBC which is current)
if we select team 2 then we need to get only amount related to BBC ie 30 ) below are the table output
Sorry for confusing .
EMPLOYEE | Amount |
1 | 36 |
2 | 36 (EXCEPT BBC DEPT) |
If selected 22
Employee | Amount |
2 | 30(only BBC) |
I will try now thanks .
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
25 | |
19 | |
15 | |
8 |
User | Count |
---|---|
58 | |
50 | |
44 | |
21 | |
19 |