Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
The situation:
I have a fctFinance table and a dimLedgerExternal table via SQL.
The fctFinance table consists among other columns of
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
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 =
Via these formums I came across
And I made this
Solved! Go to 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
Hi @DaniMak1608
please try
Result2 =
SUMX (
'fctFinance',
- 'fctFinance'[Booking value] * RELATED ( 'dimLedgerExternal'[Share Factor] )
)
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)
@DaniMak1608
Then this should work
Result2 =
SUMX (
'fctFinance',
- 'fctFinance'[Booking value]
* AVERAGEX (
RELATEDTABLE ( 'dimLedgerExternal' ),
'dimLedgerExternal'[Share Factor]
)
)
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)))
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
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]
)