Forum Discussion
Nested SUMIFS column in PowerBI
Hi, I have a table that looks like this:
| Date | Code | Value | Adjusted value |
| 01-01-2020 | VAT | 20 | 20 |
| 01-01-2020 | Total short-term debt | -100 | -120 |
| 01-02-2020 | VAT | 100 | 100 |
| 01-02-2020 | Total short-term debt | -50 | -150 |
| 01-03-2020 | VAT | 50 | 50 |
| 01-03-2020 | Total short-term debt | -75 | -125 |
I want to subtract the positive "VAT" rows from the "Total short-term debt" rows in an "Adjusted value" column whenever the month is the same, but only for "Total short-term debt" rows and not any other rows.
In Excel, I would do it like this:
=IF(B2="Total short-term debt";C2-SUMIFS($C$1:$C$7;$A$1:$A$7;A2;$B$1:$B$7;"VAT");C2)
How can I do something similar in a custom column in PowerBI?
Anonymous
Please try to create a column
Column = if('Table'[Code]="Total short-term debt",'Table'[Value]-sumx(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Code]="VAT"),'Table'[Value]),'Table'[Value])
3 Replies
- Fowmy
Super User
Anonymous
Add this as a custom column in your table in the model.Adj Value = IF( Table4[Code] = "Total short-term debt", CALCULATE( SUMX( Table4, ABS(Table4[Value]) ), ALLEXCEPT(Table4, Table4[Date]) ), Table4[Value] )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
ryan_mayuAnonymous
Please try to create a column
Column = if('Table'[Code]="Total short-term debt",'Table'[Value]-sumx(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Code]="VAT"),'Table'[Value]),'Table'[Value])
