Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
wchristiang
Frequent Visitor

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

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?

wchristiang_0-1708451696016.png

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.