Forum Discussion
How can we calculate row values for a row depending upon values in remaining row
I want to calculate the missing values for the row New Department which has the following calculation which is based on the values in other rows.
| Department | Sub-Department | Col1 | Col2 | Month | Year |
| D1 | D1-1 | 3 | 6 | 12 | 2016 |
| D1 | D1-2 | 2 | 5 | 12 | 2016 |
| D2 | D2-1 | 4 | 3 | 12 | 2016 |
| D2 | D2-2 | 3 | 5 | 12 | 2016 |
| ALL | ALL | 50 | 8 | 12 | 2016 |
| New Department | New Sub-Department | 10 | Required value |
The calculation for the required field is as follows:
Required Value = ( (Col1 * Average of Col2)(for ALL) - ((Col1 * Average of Col2)(for D1-1) +(Col1 * Average of Col2)(for D1-2)+(Col1 * Average of Col2)(for D2-1)+(Col1 * Average of Col2)(for D2-2) ) )/ Col1(for New Department) = ((8*50) -((3*6)+(2*5)+(4*3)+(3*5)))/10 =(400-(18+10+12+15))/10 = 34.5
I have tried different approaches but did not get the desired result. Please suggest a possible way of achieving this. Please help.
4 Replies
v-sihou-msftMicrosoft Employee
As Sean said, DAX can never work on cell level. And in this scenario, your table should include the fact data for lowest grain only so that you only need to aggregate the data for grand total. It's not a good practice to keep a records for "All" in the table. I suggest you separate this row into another table.
If you really need to do calculation on cell level, I suggest you use Excel.
Regards,
Thanks v-sihou-msft. We did the calculations at the sql query level for now as we could not find any way to do it at the Power BI level.
SeanCommunity Champion
Okay IF I understand what you are really asking the answer is you can't do this in PBI or even Power Pivot
"DAX works on Tables and Columns NOT Cells"
You want to have values in Col2 and then a formula in the bottom row of Col2?
At least that's how I understand your question from the sample table you've posted!
