cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
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. 

 

How can i fix this ? Please Help !!!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
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

View solution in original post

8 REPLIES 8
Highlighted
Helper III
Helper III

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

Can you provide sample data where you have issue?

Highlighted
Helper I
Helper I

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

Project NI PY Total PYNI % I CY Cost Expectation 
A16302417437123767943.91%104112029185628247.3
B4231099711587985436.51%3679047457949449.39
C2139526613846731915.45%4847409357332877.72
D390563314003412.44%13747351570016.429
Total22712100062872488636.12%190751331298627859.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 III
Helper III

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

ss.PNG

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
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
Helper I

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

Can anyone help me with this request ?

Highlighted
Community Champion
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

View solution in original post

Highlighted
Helper I
Helper I

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

Thank You so much Victor !!!! Smiley Happy

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    
RegionPO NumberInvoice QTYReceive QTYUnit Costs
AABC3280
AABC1280
BCDE23100
BCDE23100
CEFG5890
CEFG1890

 

Measurement created     
RegionPO Numbersum- Invoice QTYAverage Receive QTYRemaining QTYAverage unit costsTotal costs
AABC42280160
BCDE431100100
CEFG68-2900

 

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.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors