Manually added row to dimension table that needs to filter many conditions in value table

[bignadad]

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

[danextian]

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.

[bignadad]

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

[danextian · Accepted solution]

Sorry. That should have been just ALL(gltable).  Please see attached sample.