cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Grand Total In power BI matrix doesn't match

Hi There,

I have a Table and the grand totals for a measure dont match up. i.e If I add each row in the table in excel it doesn't give me the same result as Power BI.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion

## Re: Grand Total In power BI matrix doesn't match

@motyagi

Hi, try with this measure:

```Result-CostExpectation =
IF (
HASONEVALUE ( Table1[Project ] );
[CostExpectationM];
SUMX ( VALUES ( Table1[Project ] ); [CostExpectationM] )
)```

Regards

Victor

Lima - Peru
8 REPLIES 8
Highlighted
Helper II

## Re: Grand Total In power BI matrix doesn't match

Can you provide sample data where you have issue?

Highlighted
Helper I

## Re: Grand Total In power BI matrix doesn't match

 Project NI PY Total PY NI % I CY Cost Expectation A 163024174 371237679 43.91% 104112029 185628247.3 B 42310997 115879854 36.51% 36790474 57949449.39 C 21395266 138467319 15.45% 48474093 57332877.72 D 390563 3140034 12.44% 1374735 1570016.429 Total 227121000 628724886 36.12% 190751331 298627859.5

Cost Expectation  = ICY/(1-NI %)

The grand total is performing this at the grand total level row I just need it to add it for all the Projects so the sum is 302480591.

Thanks

Highlighted
Helper II

## Re: Grand Total In power BI matrix doesn't match

I am not sure what exact relationship you have in your modeling, but when I tried to populate and apply the formula for cost expectation. I was able to see the correct Grand Total.

Screenshot Above.

Highlighted
Helper I

## Re: Grand Total In power BI matrix doesn't match

My cost expectation is a measure and not a calculation cause of I am trying to avoid circular dependencies.

Highlighted
Helper I

## Re: Grand Total In power BI matrix doesn't match

Can anyone help me with this request ?

Highlighted
Community Champion

## Re: Grand Total In power BI matrix doesn't match

@motyagi

Hi, try with this measure:

```Result-CostExpectation =
IF (
HASONEVALUE ( Table1[Project ] );
[CostExpectationM];
SUMX ( VALUES ( Table1[Project ] ); [CostExpectationM] )
)```

Regards

Victor

Lima - Peru
Highlighted
Helper I

## Re: Grand Total In power BI matrix doesn't match

Thank You so much Victor !!!!

Highlighted
Frequent Visitor

## Re: Grand Total In power BI matrix doesn't match

Hello @ Vvelarde , I am facing the same issue even though I applied the same formulas. My total number is still not sum up correctly for row and columns totals. Below is my sample data.

 Table A Region PO Number Invoice QTY Receive QTY Unit Costs A ABC 3 2 80 A ABC 1 2 80 B CDE 2 3 100 B CDE 2 3 100 C EFG 5 8 90 C EFG 1 8 90

 Measurement created Region PO Number sum- Invoice QTY Average Receive QTY Remaining QTY Average unit costs Total costs A ABC 4 2 2 80 160 B CDE 4 3 1 100 100 C EFG 6 8 -2 90 0

Measurement I created :

1. sum- Invoice QTY= sum(Invoice QTY)

3. Remaining QTY = [sum-Invoice QTY]-[Average Receive QTY)

4. Total costs = if([Remaining QTY]<0,0,([Remaining QTY]*[Average unit costs]))

5. Total costs (to be used in Power BI matrix) = if(COUNTROWS(Values(TableA[PO Number]))=1,[Total Costs],sumx(values(Table A[PO Number]),[Total costs]))

i tried countrows and hansonvalue, both appear the same result.

Appreciate if anyone can help on this issue.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors