I’m working with a dataset exported from Exact Online by Invantive and am using Power BI to create a Profit & Loss (P&L) statement. The issue I’m facing is with the calculation of the total amount, specifically for items under Revenue (GLAccounts under Revenue). In the raw dataset, Revenue items are recorded as Credit (negative), but I need to flip the sign to positive in my reports without interfering with the total amount of P&L.
Initially, everything shows correctly in my P&L report, but the only problem is the sign for Revenue items. That’s why I employed the following approaches:
What I’ve Done:
- I used a formula to flip the sign for all Revenue items, turning them into positive values.
- For the three special items, I applied an exception to keep their values negative (as they should be costs).
- This works correctly for individual items showing on the matrix table, but the total amount of the P&L in the matrix is incorrect. The total amount shows incorrectly after I applied 1&2 solutions.
What I Want:
I need a solution that:
- Flips the sign for all Revenue items (except for the three special items).
- Ensures the three special revenue items remain negative (as costs).
- Does all this without disrupting the correct total in the matrix visual.
Has anyone encountered this issue or have suggestions on how to resolve this? Any help is greatly appreciated!