DAX formula to calculate the result between booking values and their share

[DaniMak1608]

The situation:

I have a fctFinance table and a dimLedgerExternal table via SQL.

The fctFinance table consists among other columns of

• booking values
• for each applicable ledger
• per month
• booking year, 

The dimLedgerExternal table is a DAX written table via the =DATATABLE function to provide a translation between their ledgers and our ledgers and consists of

• Ledger Ext ; the ledger our investment company uses
• Label Ext; their name
• Ledger Ours ;  our ledger that belongs their to ledger
• Share factor ; the percentage that our ledgers belongs to their ledger (it's mostly 1, but sometimes 0.3 for instances we have the salaries in one ledger, but they want have a purchase, sales and administration department ledger so we have to split the salaries values between them)

I made this DAX formula but the results weren't right. The resulting value of external ledgers, where <1 share factors are applicable, were too high. Additionaly the Total value in the visual didn't match the sum of its parts, but the total value was correct.

Result1 =

CALCULATE(SUM('fctFinance'[Booking value]),'fctFinance'[Ledger])*CALCULATE(AVERAGE('dimLedgerExternal'[Share Factor]))*-1

Via these formums I came across 

https://community.powerbi.com/t5/Desktop/Grand-Total-of-Measures-doesn-t-match-with-SUM-of-row-value...

And I made this

Result2 =

(SUMX('fctFinance',CALCULATE(SUM('fctFinance'[Booking value])*CALCULATE(AVERAGE('dimLedgerExternal'[Share Factor]))*-1)))

With this formula the individual ledgers have the correct amount, however now the Total value is wrong. 

I think the problem lies in the fact that, because neither ledgers are unique, I have to use an aggregate argument in the second part of the formula. It feels like I need something else for a solution, but I can't find what it is.

[tamerj1]

Hi @DaniMak1608 

please try

Result2 =
SUMX (
    'fctFinance',
    - 'fctFinance'[Booking value] * RELATED ( 'dimLedgerExternal'[Share Factor] )
)

[DaniMak1608]

Hi, PBI won't accept it because even though there is a relation between the two tables, it won't allow that table as input. When I manually type it, it says: "There is no relation with the existing table in the current context"

Edit: After some reading this is because the RELATED function only allows one to one or one too many relations. And both the LedgerExternal as well as the fctFinance table have a many to many with eachother (because of the share factor)

[tamerj1]

@DaniMak1608 
Then this should work

Result2 =
SUMX (
    'fctFinance',
    - 'fctFinance'[Booking value]
        * AVERAGEX (
            RELATEDTABLE ( 'dimLedgerExternal' ),
            'dimLedgerExternal'[Share Factor]
        )
)

[DaniMak1608]

Thanks for your quick response. 

It works, however it gives the exact same result as my own Result 2 😆

For some reason the total value is wrong

Result2 =
(SUMX('fctFinance',CALCULATE(SUM('fctFinance'[Booking value])*CALCULATE(AVERAGE('dimLedgerExternal'[Share Factor]))*-1)))

[v-easonf-msft · Accepted solution]

Hi, @DaniMak1608 

Please try the following formula to recalculate the total value.

Result =
SUMX ( VALUES ( 'fctFinance' ), [Result2] )

or the following formula to replace the total value of [Result2] with the total value of [result1]

Result =
IF ( ISINSCOPE ( 'fctFinance'[ individual ledgers] ), [Result2], [Result1] )

Best Regards,
Community Support Team _ Eason

[tamerj1]

@DaniMak1608 

Can you share a screenshot of your table visual?
I have a feeling that this is what you want

Result2 =
SUMX (
    CROSSJOIN ( 'fctFinance', 'dimLedger' ),
    - 'fctFinance'[Booking value] * 'dimLedgerExternal'[Share Factor]
)