topic Re: DAX formula to calculate the result between booking values and their share in DAX Commands and Tips

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

Anonymous — Tue, 17 May 2022 07:02:07 GMT

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-values/m-p/145769

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.

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

tamerj1 — Tue, 17 May 2022 10:56:48 GMT

Hi Anonymous

please try

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

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

Anonymous — Tue, 17 May 2022 11:18:38 GMT

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)

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

tamerj1 — Tue, 17 May 2022 11:28:06 GMT

Anonymous
Then this should work

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

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

Anonymous — Tue, 17 May 2022 11:44:43 GMT

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)))

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

tamerj1 — Tue, 17 May 2022 12:04:23 GMT

Anonymous

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] )

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

v-easonf-msft — Tue, 07 Jun 2022 06:38:27 GMT

Hi, Anonymous

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