Power Pivot Grand Totals

[Hsilva]

Hi, I understand that one solution for the grand totals error is to use the SUMX function. The problem is I do not know how to write the function when I am working with multiples table and measures.

I have the following data set: The idea is to calculate the total recharge for project = total hours worked in a project / total hours worked in a fortnight * total Expense in a fortnight.

Table1: Hours and Expense in a fortnight

Project	Emp ID	Date	Value	Category
SWA	10079788	25/08/2019	76	Hour
SWA	10079788	22/09/2019	76	Hour
SWA	10079788	20/10/2019	76	Hour
SWA	10079788	15/12/2019	76	Hour
SWA	10079788	12/01/2020	76	Hour
SWA	10079788	9/02/2020	76	Hour
LIS	10092380	30/06/2019	18807.1	Expense
LIS	10092380	14/07/2019	18807.1	Expense
LIS	10092380	28/07/2019	18807.1	Expense
LIS	10092380	11/08/2019	18807.1	Expense
LIS	10092380	25/08/2019	18807.1	Expense

Table2=Hours Worked in a Project

ID	Date	hours	Project
10079788	22/07/2019	4	LIS
10079788	25/11/2019	2.5	LIS
10079788	9/09/2019	0.5	LIS
10092380	2/12/2019	3.5	SWA
10092380	9/12/2019	4	SWA
10092380	8/07/2019	2.5	SWA
10092380	8/07/2019	2.2	SWA

I have the following measures:

• Total hours worked in a project = sum(table2[hours])
• Total hours worked in a fortnight = Calculate(sum(table1[Value]),Category=”Hour”))
• Total Expense in a fortnight = Calculate(sum(table1[Value]),Category=”Expense”))
• Total % to charge = Total hours worked in a project / Total hours worked in a fortnight
• Total $ to charge = Total % to charge * Total Expense in a fortnight

Then I got the wrong grand total in the pivot table:

Recharge $ FN	FN
id	15	16	Grand Total
LIS	331.84	656.62	961.69
SWA	1,494.67	2,953.73	4,452.79
Grand Total	4,767.90	13,202.55	17,004.04

Thank you very much.

Humberto

[Hsilva · Accepted solution]

Thank you very much UlfBergqvist.

Yes, you are right the problem is the grand totals. Just one more question, in the NewMeasure below, the table "Project" in the Sumx formula, is a new one or an existing one?

Thanks

Hsilva  

[Hsilva]

Pivot table attached

[Anonymous · Accepted solution]

Hi, this is starting to get a little bit complicated. But since the calculateion is ok on FN 15 and FN 16, my best guess right now is to add the FN table to the crossjoin as well to do the calculation by project/employee and FN.

/ Ulf

[Hsilva]

Hi UlfBergqvist, thank you very much again.

it works now by addin the FN table to the CROSSJOIN fuction ( I did it with the Calendat table and was wrong)

All good now

Hsilva

[Anonymous]

You could also try to replace the CROSSJOIN with SUMMARIZECOLUMNS(Staff[Emp ID], Projects[id], Calendar[FN]).

[Anonymous]

A little bit hard to understand exactly what you are showing in the pivot table based on the table data (since column names are different). Are the tables connected with any relationsships? You need to connect them to be able to use attributes from one table and show measures that use data from the other table. For example, create a new table with one row for each project and connect to both tables. Create another table with employees and connect to both tables. Same with dates.

Another solution could be to merge the two tables by adding data from table 2 to table 1 (with another category) and calculate in the same way:

Total hours worked in project = Calculate(sum(table1[Value]),Category=”Project”))

A good data model always makes life easier in Power BI.

[Hsilva]

Thank you UlfBergqvist for your feedback and apologies for the inconsistency betwee the tables and the pivot table. The idea was to show that row by row and by periods the calculation is ok, the problem in with the grand totals.

Yes I have all my tables linked with realtionship, see model below.

[Anonymous]

Ok! Model looks good!

So, the problem is that the [Total $ to charge] has to be calculated by project (or project and employee, it depends on your requirements) and then summarized. This is because you have a % calculated that will give a different result when you do it on all rows in the table.

You can try something like this to make the calculation by project:

NewMeasure = SUMX('Projects', CALCULATE([Total $ to charge]))

If you want have the calculation done grouped by both project and employee, you need to replace 'Projects' in above calculation with some kind of combination of all projects and staff. CROSSJOIN('Projects', 'Staff') should work if you don't have huge amounts of data.

[Hsilva]

Hi UlfBergqvist, sorry for asking again.

I have still a small problem, now the grand total by row (staff and projects) is ok after using the CROSSJOIN function, however the grand total by columns (Fortnights from Calendar table) is not correct.  Am I missing something else?

I tried to join the calendar table in the CROSSJOIN function, but the result was empty or blank.

Thanks again

Hsilva

[Anonymous]

@HsilvaPerhaps you can post an example of what you get on the row total and what you want it to be. It's always easier to find the problem with some real examples!

Also, when you mark a solution, do it on the post that solves the problem, not your own 🙂

[Hsilva]

Hi UlfBergqvist, thank you and apologies for the solution mistake (I was pretty sure I clicked in the right one, obviously I didnt :()

Please see below the current PT:

The Total Recharge $ for fortnights 15 and 16 (columns K and L) are OK both of them, I used the SUMX function.

(SUMX(CROSSJOIN(Staff,Projects),CALCULATE([Recharge $ FN])))

However the Gran Total (column O) for some staff are wrong, basically it is a multiplication of the Total % Project Charge FN * Total $FN (Column M*N) instead of adding FN15 + FN16.

I manually added column Q to show the right value = FN15 + FN16 and Column Q with the differece.

[Hsilva · Accepted solution]

Thank you very much UlfBergqvist.

Yes, you are right the problem is the grand totals. Just one more question, in the NewMeasure below, the table "Project" in the Sumx formula, is a new one or an existing one?

Thanks

Hsilva  

[Hsilva]

Thank you very much UlfBergqvist  for your time and help.  Now I understand a bit more of these DAX formulas.

The model is working perfect.

Cheers

Hsilva

[Anonymous]

It's the Projects table you have in your model. I trust you follow the golden rule of creating models: All fields in a fact table should be hidden from view. Only dimensions should be exposed.

[Hsilva]

Thank you darlove for clarifying the table, and for your advice about hiding field in fact table. I will follow it.

Cheers

Hsilva