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.
Dear Community,
I am a new user and working on my first dashboard in Power BI. The file I got has data in weekly frequentcy from different departments, these are all sorted on date. One column is the amount of employees from the department on that specific date, I want to calculate what the total amount of workers is on that date and how many percentage of the total employees are working for that department.
The table is like this
Column A(Date) Column B(Departement) Column C (Profiles,which is how many people there are) Column D (Variable X)
19-02-2017 Department A 8
19-02-2017 Department B 9
19-02-2017 Department C 293
26-02-2017 Department A 15
26-02-2017 Department B 8
26-02-2017 Department C 300
So I want to calculate
Total Employees on that date
Percentage of employees working for that department on that date
Thanks in advance,
0xygen27
Hi @0xygen27
Try the following
1. Create a measure called TotalByDate
TotalByDate = Calculate(sum(Table3[ColumnC]), ALLEXCEPT(Table3,Table3[ColumnA]))
2. Createa measure called %toTotal
%toTotal = Divide(sum(Table3[ColumnC]),[TotalByDate])
Replace all Table3 by your tablename. Sample output with the data provided by you.
If this resolves your issue, please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
Hi @0xygen27,
You can try to use below formula which about calculate the running total:
Measure:
Running total= var currDepart= LASTNONBLANK(Table[Department],[Department]) return SUMX(FILTER(ALLSELECTED(Table),Table[Department]=currDepart&&Table[Date]<=MAX(Table[Date])),[Profiles])
Regards,
Xiaoxin Sheng
Power BI gives me the return: The MAX function only accepts a column reference as an argument.
Hi @0xygen27,
>>Power BI gives me the return: The MAX function only accepts a column reference as an argument.
Can you share us your formula?
Regards,
Xiaoxin Sheng
Running total = var currDepart= LASTNONBLANK('database'[Profiel],'database')
return
SUMX(FILTER(ALLSELECTED('database'),'database'[Profiel]
=currDepart&&RELATEDTABLE(Datedimension)<=MAX(Datedimension)),'database'[Profiel])
Hi @0xygen27,
>>RELATEDTABLE(Datedimension)<=MAX(Datedimension)
You need to specify a column from that table, max function not works on table.
In addition, if your visual has created from different table, it will be help if you share some sample data.
Regards,
Xiaoxin Sheng
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |