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
Raveein26
Regular Visitor

Getting wrong row subtotals in matrix table.

In our Power BI report, we have a calculated column (Column C) defined as follows: Column C = Column A / Column B. The requirement is to get the subtotals for Column C as the sum of individual rows of the calculated column, not as SUM(Column A) / SUM(Column B). We need the sum of values of Column C for subtotals, not an aggregate of Column A and Column B separately.

 

Additionally, we have another calculated column (Column E) defined as: Column E = (Column C - Column D). The issue arises when trying to obtain subtotals for Column E. The current behavior is that Power BI is taking SUM of Column C and Column D and then applying the subtraction, rather than summing the individual rows of Column E.

Below are the Sample data and Formula :

C = A / B
E = C - D

Note : Need SUM value in SubTotals Column C = 5.5 Column E = 3



Columns

A

B

C

D

E

 

10

4

2.5

1.5

1

 

15

5

3

1

2

SubTotals

25

9

2.777778

2.5

0.277778

9 REPLIES 9
v-yangliu-msft
Community Support
Community Support

Hi  @Raveein26 ,

 

Generally, the calculated columns do not have subtotal errors in the matrix, this generally occurs in measure, can you share sample data and sample output in tabular format? Or sample pbix after removing sensitive data. We can understand the issue better and help you.

vyangliumsft_0-1697001272190.png

You can also try the following dax:

MeasureC_if=
var _table1=
SUMMARIZE('Table','Table'[Group],"Value",[MeasureC])
return
IF(
    HASONEVALUE('Table'[Group]),[MeasureC],SUMX(_table1,[Value]))

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I had three group in row filed called Country, Country region and Pin code. In your measure if I use only one  field  country or Country region or pin code I am getting wrong total how to resolve it?

Raveein26_0-1697179842104.png

 

How to attach my power bi sample file for your reference. I could not able to see insert option here or attach file option?

Hi @v-yangliu-msft 

You can see your matrix visual for Measure C column you are getting a wrong total. It should be 5.50  but you are getting 5.00.


Same issue I am facing in my matrix. Please find the below screenshot. For C column I am getting total as 17.22 but It should be  23.61.

Raveein26_0-1697095280694.png

 



riyasiak
Frequent Visitor

Recommend you to create and use Custom Columns under Transform rather than calculated DAX:

1. Create a Custom Column C and use the formula Column A / Column B

2. Similarly, create Custom E and us the formula Column C - Column D

Make sure the new columns are numerical

Apply the changes, and check the Sub-Totals

Raveein26
Regular Visitor

It is not working as i expected.

Share sample pbix file to help you

How to attache my power bi sample file. I could not able to see insert option here or attach file option?

Ahmedx
Super User
Super User

pls try this

c fixid = SUMX(VALUES('table'[ColumnsA]), [c] ) ---  [c] = C = A / B

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.