March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have a table called glAccount
It has a relationship with glEntry
The point of this is to display the 3 level heiarchy in my matrix table
AccountGroup, AccountSubGroup, AccountNoName
This works fine.
The problem is I have a few custom rows I need to add.
One example is NP (Net Profit)
To get it in the dimension table i added a custom table and manually entered the data then appended with the glAccount table.
All the values are from the glEntry tables. I don't use the values in this glAccount table.
How this NP is calculated is by filtering glAccountNo.
Here is a custom column I added to glEntry as I was testing.
isNP = IF(
and(glentry[gLAccountNo] >= 4010,glentry[gLAccountNo] <= 4200) ||
and(glentry[gLAccountNo] >= 4410,glentry[gLAccountNo] <= 4600) ||
glentry[gLAccountNo] = 4900 ||
and(glentry[gLAccountNo] >= 5000,glentry[gLAccountNo] <= 5090) ||
and(glentry[gLAccountNo] >= 6010,glentry[gLAccountNo] <= 6320) ||
and(glentry[gLAccountNo] >= 6420,glentry[gLAccountNo] <= 6499) ||
and(glentry[gLAccountNo] >= 6510,glentry[gLAccountNo] <= 6599) ||
and(glentry[gLAccountNo] >= 7000,glentry[gLAccountNo] <= 8150) ||
and(glentry[gLAccountNo] >= 8200,glentry[gLAccountNo] <= 8285) ||
and(glentry[gLAccountNo] >= 8400,glentry[gLAccountNo] <= 8491) ||
and(glentry[gLAccountNo] >= 8600,glentry[gLAccountNo] <= 8680) ||
glentry[gLAccountNo] = 8700 ||
and(glentry[gLAccountNo] >= 8800,glentry[gLAccountNo] <= 8950) ||
and(glentry[gLAccountNo] >= 8990,glentry[gLAccountNo] <= 8991) ||
and(glentry[gLAccountNo] >= 9997,glentry[gLAccountNo] <= 9998),
glentry[amount],0)
Of course this won't add it to my table matrix.
The value is in glAccount but I can't figure out how to relate it to the values table so it will show up on my report
If this isn't possible how else can I do this.
It seems like it should be simple. I just need to relationship from the NP glAccount to match many glAccountNo's
Solved! Go to Solution.
Sorry. That should have been just ALL(gltable). Please see attached sample.
Proud to be a Super User!
Hi @bignadad
This is always one of the challenges when replicating financial statements in Power BI. It is either our pet peeve to make it follow the FS format or it is the stakeholder who thinks that Power BI can work like Excel. And yes, appending an extra row to the accounts table is one of the steps (or doing it in DAX, depending on the use case). But why use such a long formula for NP which is usually just Sales - COGS - Expenses or the net of Debits and Credits of nominal accounts in a given period. It should be as simple as this.
=
SWITCH (
SELECTEDVALUE ( GLAccountTable[Account] ),
"Net Profit",
CALCULATE (
[NP Measure],
FILTER (
ALL ( GLAccountTable[Account] ),
GLAccountTable[Account] = "Net Profit"
)
),
[PnL Measure]
)
ALL ( GLAccountTable[Account] ) tells DAX to return the value of NP if the account name is Net Profit.
Proud to be a Super User!
Are you suggesting to use the measure you provided as the values for the matrix table?
SWITCH (
SELECTEDVALUE ( glAccount[no] ),
"NP",
CALCULATE (
[Net Profit],
FILTER (
ALL ( glAccount[no] ),
glAccount[no] = "NP"
)
),
[Total]
)
I tried this but it didn't work.
The relationship between glAccount and glEntry is the gl account no. There are not entires in glEntry with that table beacuse its are totals.
Sorry if i missed your answer, but I'm not seeing how this works.
the gl account no in glAccount is NP
All the other values are show up with that measure but not NP
Sorry. That should have been just ALL(gltable). Please see attached sample.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
133 | |
90 | |
88 | |
64 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
70 | |
68 |