- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 !!!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, try with this measure:
Result-CostExpectation = IF ( HASONEVALUE ( Table1[Project ] ); [CostExpectationM]; SUMX ( VALUES ( Table1[Project ] ); [CostExpectationM] ) )
Regards
Victor
Lima - Peru
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you provide sample data where you have issue?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
My cost expectation is a measure and not a calculation cause of I am trying to avoid circular dependencies.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can anyone help me with this request ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, try with this measure:
Result-CostExpectation = IF ( HASONEVALUE ( Table1[Project ] ); [CostExpectationM]; SUMX ( VALUES ( Table1[Project ] ); [CostExpectationM] ) )
Regards
Victor
Lima - Peru
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank You so much Victor !!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
05-10-2024 10:55 AM | |||
05-17-2024 04:33 AM | |||
08-13-2023 11:33 PM | |||
Anonymous
| 08-01-2023 09:25 PM | ||
12-13-2023 09:18 AM |
User | Count |
---|---|
136 | |
107 | |
86 | |
58 | |
46 |