Grand Total In power BI matrix doesn't match

[motyagi]

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. 

How can i fix this ? Please Help !!!

[sqlguru448]

Can you provide sample data where you have issue?

[motyagi]

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

[sqlguru448]

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.

[motyagi]

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

[motyagi]

Can anyone help me with this request ?

[Vvelarde · Accepted solution]

@motyagi

Hi, try with this measure:

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

Regards

Victor

[motyagi]

Thank You so much Victor !!!! 

[89898]

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)

2. Average Receive QTY = Average (Receive 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.