Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all, I need your help. I am new to DAX and everything I tried gives me the wrong answer.
We manually calculate an employees FTE Value which is the amount charged per module divided by the total charged.
The issue I have is the amount charged per module. I can see it in a pivot only. How can I calculate the amount charged by module by employee? Columns I am using are as follows:
emp id, emp name, module, actual $ charged, fiscal period.
modules are part of a large program so some people charge multiple modules and I need to calculate the amount per module divided by the total amount charged by each employee.
Example: Paul is 100/100=1 FTE value.
John is 50/100=0.5FTE to one module and 50/100=0.5 to another module.
When I tried this on my own its only dividing by the total in one module and not all the modules.
I hope I explained this well enough.
Thank you for all those that will help me.
@PowerPaul , something like this
sumx(Table, divide( Table[actual $ charged],Table[module]))
if not
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi Amitchandak, thanks for your response. It did not work because Module is a text field. Its an abbreviation of module name.
Here is some sample data (not actual data but similar format)
Module EmpID EmpName Actual $
WM 1001 Dan M 114.50
CL 1019 Bill S 837.9
AM 1019 Bill S 63.64
ISL 1019 Bill S 1163.17
WMS 1001 Dan M 2406
the solution would have to aggregate the total for each employee for each module and divide it by the total of all charges by employee.
@PowerPaul , like one of the 2 ?
sumx(values(Table[EmpID]) , calculate(divide(sum(Table[Actual $]), distinctCOUNT(Table[Module]))))
or
sumx(values(Table[Module]) , calculate(divide(sum(Table[Actual $]), distinctCOUNT(Table[EmpID]))))
Thanks again Amitchandak.
I am getting an error message:
Semantic Error: too many arguments were passed to the sum function. The maximum argument count for the function is 1.
Note: I am using Excel 365 get and transform/power pivot.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |