Forum Discussion

motyagi's avatar
motyagi
Helper I
8 years ago
Solved

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 ? P...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    motyagi

     

    Hi, try with this measure:

     

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

    Regards

    Victor

motyagi's avatar
motyagi
Helper I
8 years ago

Thank You so much Victor !!!! :smileyhappy:

89898's avatar
89898
Frequent Visitor
7 years ago

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.