Grand total different sum of rows

[mariajoaomp]

Hello, 

I have a project where I have two factual tables and I have a metric which is the divide labor cost by average fte. The labor cost metric goes to one table (laborcost) and the average fte goes to another table (headcounts). The metric is fine on the line but the grand total is different from the sum of the lines in the table. I've tried sumx, summarize, putting everything together in a single factual table. Here in these fact tables I have no way of linking them. Have you experienced something similar?

Thank you

[Anonymous]

HI @mariajoaomp,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

In addition, you can also take a look at the greg's blog about handle with measure expression and multiple aggregations if it suitable for you scenarios:

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[Moetazzahran]

Hello, 
Can you please attach a sample dataset or screenshots of your sample data and how your visual looks?
Thank you

[mariajoaomp]

here's the problem. The sum of the total is not the sum of the rows. The average cost metric is Average Cost SUMX:= DIVIDE(
[LabourCost SUMX],CALCULATE(
[Average FTE SUMX],ALL(Account)),0)

and auxiliary metrics are LabourCost and AverageFTE 

Labour Cost SUMX :=
CALCULATE (
SUMX ('UNION', 'UNION'[ValueLaborCost] ),
FILTER (
Account,Account[INDEX Level LabourCost]=5)
)

Average FTE SUMX :=
CALCULATE (
SUMX ('UNION', 'UNION'[ValueHeadCount] ),
FILTER (
Account,Account[INDEX Level FTE]=4)
)

The structure/fields of the table are the same. In this example, I've even put everything in a single table that I've called union, but the accounts of the laborcosts and the accounts of averagefte are different and so I can't link them.

Can anyone help me?

[Anonymous · Accepted solution]

Hi @mariajoaomp,

For multiple level aggregations in measure, I'd like to suggest you consider to using summarize function to aggregate the first level and use iterator function(e.g. sumx, averagex) on above result to apply the second level aggregation:

All the secrets of SUMMARIZE - SQLBI

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng