DAX Measure help - GL Account Adjustment

Question

Hello Community -&nbsp;&nbsp;I am working on a Trial Balance where we have intercompany elimination entries.&nbsp; Below is the current measure for one of the Accounts (4300) that has intercompany activity:&nbsp;O&amp;E Interco 4200 =

CALCULATE(
        SUM('helper GLBalances_ByYearMonth'[Closing Balance]) * -1,
        FILTER(
            'helper GLBalances_ByYearMonth',
            'helper GLBalances_ByYearMonth'[Account Code] = "4300" &amp;&amp;
            'helper GLBalances_ByYearMonth'[Company Code] &gt;= "00010" &amp;&amp;
            'helper GLBalances_ByYearMonth'[Company Code] &lt;= "09001"
        )
    )&nbsp;This measure gets me the exact value that I need to see for the intercompany elimination for account 4300.&nbsp; My issue comes in the next part.&nbsp; I need to offset that intercompany elimination in account 4200, which has no activity, but it is just a holding account that will total the inverse of the measure shared above.&nbsp; I have tried multiple different options, but I cannot get a measure to work to pull in the inverse of the 4300 elimination entry to show up for GL Account 4200.&nbsp;Can someone please provide some insight or assistance on how I can acheive this 'adjusting' entry with a DAX measure?&nbsp;Thank you Community!Ryan F.

danextian · Answer

As Ritaf1983&nbsp; mentioned, please provide a workable sample data.

ritaf1983 · Answer

Hi&nbsp;RyanFiting&nbsp;
Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.&nbsp;&nbsp;
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

danextian · Answer

Hi RyanFiting,
Depending on the current filters, if your ta
You need to have a separate accounts table instead of having everything in one. This will simplify the calculations. For example, if there isn't a row for 4200 in your fact table for a given year, you cannot make it appear in a visual for that given year even if you assign a value to it using a measure.&nbsp; You can create one using DAX or M. Example DAX calculated table:
&nbsp;
Accounts =
DISTINCT('helper GLBalances_ByYearMonth'[Account Code])
&nbsp;
Once the account table is setup, create a a one-to-many single direction relationship&nbsp; from the Accounts[Account Code] to 'helper GLBalances_ByYearMonth'[Account Code].&nbsp; You need to start using this new account column going forward.
Create this measure:
Account4200 =
CALCULATE (
    SUM ( 'helper GLBalances_ByYearMonth'[Closing Balance] ) * -1,
    Accounts[Account Code] = "4300",
    KEEPFILTERS ( 'helper GLBalances_ByYearMonth'[Company Code] &gt;= "00010"
        &amp;&amp; 'helper GLBalances_ByYearMonth'[Company Code] &lt;= "09001" )
)

To return this value along with other values, create another measure:
IF (
    SELECTEDVALUE ( Accounts[Account Code] ) = "4200",
    [Account4200],
    [other value measure]
)

&nbsp;

ryan_b_fiting · Answer

Thanks&nbsp;danextian&nbsp;there is a row for Account code 4200 in my fact table, but the total value in there is NOT equal to the offset, that 4200 account contains more in there than just the offset.&nbsp; And being this is at a summarized level, we do not have anyway to filter out the transactions that make up the offset.&nbsp; See below how the sample is:&nbsp;&nbsp;So account 4200 contains the $125k, but also contains other values.&nbsp; The ultimate goal is toe get +$125k into the interco Eliminations on the Account Code line of 4200.&nbsp; Being we do not have each individual transaction in the warhouse table, we need to just create a measure that can offset the value in account code 4300 each month.&nbsp;Hopefully that makes sense.

ryan_b_fiting · Answer

Thanks&nbsp;Ritaf1983&nbsp;.Below is a screen shot of what I am looking at now:&nbsp;Account 4300 is a full intercompany eliminations account, so everything in that account is allocated to the interco elimination entry.&nbsp; However,&nbsp;account 4200 is more than just an elimination account, but it does contain the $125k (in the example above) in interco transactions, but also contains other values.&nbsp; The ultimate goal is to get the +$125k into the interco Eliminations on the Account Code line of 4200.&nbsp; Being we do not have each individual transaction in the warhouse table, we need to create a measure that can offset the value in account code 4300 each month.

Forum Discussion

DAX Measure help - GL Account Adjustment

6 Replies

Related content

Percentage adjustment based on selecting an account

Recursive loop Measure /Auto Adjustable Measure /Circular Dependency Issue While Adjusting values

Adjust this measure to show totals

Community account support

Adjusting font size with DAX measure