Totals in table (matrix)

[Endurion]

Hi,

The problem that I have is that the totals in a matrix do not count up correctly in the totals. Other values are correct. With some demo-data I have these values. 

As you can see there are mulitple levels within this matrix. The calculation behind this value is a messuement that combines multible other messuements that are made. This is because this value will become a totale messuement of those other values.

The caluclation (redacted) as it stands now is as followed:

VAR V1= [calculation]-[calculation]

VAR V2= CALCULATE(SUM('Table'[number]),'Tablel'[string]="Month")

VAR V3 = [calculation]

VAR V4 = [calculation]

RETURN

-V1*V2*V3*V4

I have tried to SUMMARIZE but im not very femiliair whit that function. Also tried a SUMMARIZE with a nested SUMX.

Anyone has tips?

[Anonymous · Accepted solution]

Hi  @Endurion,

You can create another new measure as below and put this new measure to replace the original measure onto the matrix visual...

Measure =
SUMX (
    GROUPBY ( 'Table', 'Table'[level1field], 'Table'[level2field] ),
    [Original measure]
)

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Endurion]

Hi @Anonymous , thanks! This sollution works and is something I did not tought of before. 

Again thanks, now I can build this report.

[technolog]

What results you get if you just add column to the matrix withous measures?

[Endurion]

How do you mean this? Add every collum individualy? Because that is not an option. The calculation is made within Power BI and not in the source files.

[technolog]

I mean just add all columns from fields panel 

[Endurion]

Already tried that.

[technolog]

Could you make file with test data?

[tamerj1]

Hi @Endurion 

If all the columns involved in the matrix hierarchy are from the same table then umyou need to perform a SUMX of the measure over the SUMMURIZE table based on these columns. For example in you matrix looks like you at least 4 columns stacyin the hierarchy of the matrix. Then you need to summarize the table by these 4 columns then iterate over the summary table using SUMX to evaluate and sum the measure over each row of this summary table in order to generate correct subtotals. Something like 

SUMX ( SummaryTable, [Measure] )

if the columns are all from different dimension tables then you need to perform the CROSSJOIN of the VALUES of these columns instead of the SUMMARIZE. It really depends on your data model and the existing relationships 

[Endurion]

Well there are more tables involved because I need to look into the diffrences between budget and actual. So there is a table with the budget and an table actual. I will try the CROSSJOIN.

[tamerj1]

@Endurion 

Summarize each table by it's invloved columns then crossjoin the two summary tables. 

[technolog]

Hi @Endurion !

It's not enough information, but you can try:

VAR V2= CALCULATE(SUM('Table'[number]),FILTER('Table', 'Table'[string]="Month"))

Maybe it helps you)

[daXtreme]

@Endurion 

With this scarce information it's not possible to tell you what's wrong. You have to be more descriptive and actually demonstrate the issue fully. Showing the numbers like this is not enough.

[Endurion]

Well I’m trying to calculate the effect (financial) of events. All data in in one table with a column specifying if the values are month, cumulative or last year. The problem is that the green cells with – this demo data – are correct but the red cells are not a addition of the green cells.

This is probably due to the calculation on that level. Picking up raw data and calculating it again. But I need to find a way to make a addition of the total without changing the underlying values. I mean pick up your calculator and add the values up, it does not match.

---

The more deeper calculation is as following:

VAR V1= [calculation1]-[calculation2]

   calculation1 = CALCULATE(SUM(Table1'[number1)])/SUM('Table1'[number2]), 'Table1'[string]="Month")

calculation2 =

   IFERROR(SUM('table2'[number2])/CALCULATE(SUM('Table2'[number]),'Table2'[String]="Permanent/Fixed employee"),0)

VAR V2= CALCULATE(SUM('Table'[number]),'Tablel'[string]="Month")

VAR V3 = [calculation4]

   VAR V31 = SUM('Table1'[number3])

   VAR V32 = AVERAGE('Table2'[number2])

   VAR V34 = [calculation41]

   VAR V35 = [calculation51]

   RETURN

   V31/(V32*V34*V34)

VAR V4 = [calculation5]

   Calculation5 = SUM('Table2'[number4])/SUM('Table2'[number 5])

RETURN

-V1*V2*V3*V4

---

Does that give you enough information @daXtreme @technolog?

[technolog]

You don't need to use filter in your case 

VAR V2= SUM('Table'[number])

Please send screenshots without filter.

[Endurion]

Well I do, because the total table has more than one dimention. Also the problem is not the calculation but the fact that the totals in the table do not add up.