topic Re: How to calculate a Column Subtotal having the result sum of coluns in Developer

How to calculate a Column Subtotal having the result sum of coluns

wchristiang — Tue, 20 Feb 2024 18:05:26 GMT

I have the following example of a situation in a Power BI matrix:

	MONTH 1	MONTH 2	TOTAL
VALUES	5	2	2,85

See that the result of my DAX formula in month 1 is 5 and in month 2 is 2.

So the sum should be 7, but bringing 2.85.

What’s the point of that?

Answering: The formula DAX has sum, multiplication and division causing that month to bring a result and the total that is the formula applied in the two months bring another one.

Example:

Month 1

(3 + 7) / 2 = 5

Month 2

(6 + 4) / 5 = 2

Month 1 and month 2 together the total is considered the sums of dividends by the sum of the divisors of the two months together to bring 2.85 as a result as follows:

(3 + 7 + 6 + 4) / (2 + 5) = 2,85

Is there any way for the total column that is already automatic from Power BI as shown below, to sum the columns and bring the value 7 instead of 2.85?

Re: How to calculate a Column Subtotal having the result sum of coluns

Anonymous — Wed, 21 Feb 2024 01:57:24 GMT

Hi @wchristiang,

This sounds like a common issue appears when you try to apply multiple aggregations in measure expressions.
For this scenario, you can refer the Gerg’s blog to know more about this and how to handle it.

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

Re: How to calculate a Column Subtotal having the result sum of coluns

wchristiang — Sat, 09 Mar 2024 13:50:38 GMT

In the mentioned example in this post I exemplified the logic of the problem, but in my case I’m using a very complex DAX, whose data source is recalculated several times in a big formula, where the final values are results of several tables with various calculated attributes

In the example of the resolution posted in Measure Totals, The Final Word is suggesting to use SUMMARIZE and HASONEVALUE, but for both I have to use one table and one attribute of the table, but as I said before, the origin of my data have already been well modified until I have the example I quoted, so I can’t point out a single table and a single attribute, because the information is the result of multiple calculations of multiple tables and attributes with different conditions.

How can I proceed in this case?

If anyone can help me I can forward the pbix file and show the exact point in your e-mail.

Thank you.