how to sum a value by group

[Anonymous]

Hello,

I would like to create a measure (total Expenses amount) by project ('dim project'[Projectskey]), employeeID ('Dim employee'[EmployeeID] and by Billing Status (fact expenses '[Billing Status]).

I have try with sumx(values('dim project'[Projectskey]),calculate(sum('fact expenses'[amount]))) but I did not found the proper syntax to add the two others variables of the group by statement.

Does someone know how to this calculation in Dax

Thanks in advance for your help.

alepage

[Michiel]

This is very easy if you let Power BI take care of the 'by ...' part. Your [Total expenses amount] is probably something like a sum over a column in a fact table, say SUM(Expenses[Amount]). If you create relationships from the Expenses table to the Projects table and the Employee table, you can create a report with fields from Project and Employee (and Billing status for that matter) and the total expenses amount will be filtered according to the fields selected.

[Anonymous]

Hello,

I dont want to sum using selected filter.  I want to create a measure which will give me the total amount group by

project, person and bill status. Then I will start from that measure to populate a table required by my client.

Do you know how to calculate this sum by group in dax

Regards,

[Michiel · Accepted solution]

Hmm, in my view, the total amount grouped by project, person and billing status is just, well, the total amount.

But anyway, if you really want to do this:

TotalAmount = SUM(Expenses[Amount])

TotalAmountGroupBy =
SUMX( CROSSJOIN(Project, Employee),
   CALCULATE( SUMX(VALUES(Expenses[Billing Status]), [TotalAmount] )
)