topic Re: Totals in table (matrix) in DAX Commands and Tips

Totals in table (matrix)

Anonymous — Mon, 01 Aug 2022 08:37:31 GMT

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?

Re: Totals in table (matrix)

daXtreme — Mon, 01 Aug 2022 08:56:51 GMT

Anonymous

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.

Re: Totals in table (matrix)

technolog — Mon, 01 Aug 2022 09:01:09 GMT

Hi Anonymous !

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

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

Maybe it helps you)

Re: Totals in table (matrix)

Anonymous — Mon, 01 Aug 2022 09:28:36 GMT

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?

Re: Totals in table (matrix)

tamerj1 — Mon, 01 Aug 2022 10:09:03 GMT

Hi Anonymous

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

Re: Totals in table (matrix)

Anonymous — Mon, 01 Aug 2022 10:29:36 GMT

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.

Re: Totals in table (matrix)

tamerj1 — Mon, 01 Aug 2022 10:57:51 GMT

Anonymous

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

Re: Totals in table (matrix)

technolog — Mon, 01 Aug 2022 11:02:53 GMT

You don't need to use filter in your case

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

Please send screenshots without filter.

Re: Totals in table (matrix)

Anonymous — Tue, 02 Aug 2022 12:13:42 GMT

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.

Re: Totals in table (matrix)

technolog — Tue, 02 Aug 2022 12:17:31 GMT

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

Re: Totals in table (matrix)

Anonymous — Tue, 02 Aug 2022 12:20:50 GMT

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.

Re: Totals in table (matrix)

technolog — Tue, 02 Aug 2022 12:25:14 GMT

I mean just add all columns from fields panel

Re: Totals in table (matrix)

Anonymous — Tue, 02 Aug 2022 12:30:02 GMT

Already tried that.

Re: Totals in table (matrix)

technolog — Tue, 02 Aug 2022 16:09:25 GMT

Could you make file with test data?

Re: Totals in table (matrix)

Anonymous — Thu, 04 Aug 2022 07:35:49 GMT

Hi Anonymous,

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

Re: Totals in table (matrix)

Anonymous — Thu, 04 Aug 2022 07:43:48 GMT

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

Again thanks, now I can build this report.