I had a query where I want to match the values of credit side with the debit side based on ID.
I got help from one of the member to create formula, it checks the sum and give back the result. But on transaction level it is not matching the value. As shown below sum matches the value but on transaction level it still shows -1 i.e. Not Matched.
Equal = IF(CALCULATE(SUM(Query1[F_NOM_CREDIT]), ALLEXCEPT(Query1,Query1[F_NOM_VEH_ID],Query1[F_NOM_PERIOD]))=CALCULATE(SUM(Query1[F_NOM_DEBIT]), ALLEXCEPT(Query1,'Query1'[F_NOM_VEH_ID],Query1[F_NOM_PERIOD])), 0,-1)
Some how I managed to filter them by the Vehicle ID and Nominal Period but still struggling to show as matched on transaction level.
Please try with following measure.
Equal2 = IF ( CALCULATE ( SUM ( 'Query1'[F_NOM_CREDIT] ), ALLEXCEPT ( 'Query1', 'Query1'[F_NOM_VEH_ID] ) ) = CALCULATE ( SUM ( 'Query1'[F_NOM_DEBIT] ), ALLEXCEPT ( 'Query1', 'Query1'[F_NOM_VEH_ID] ) ), 0, -1 )
What is the expected result for Equal2 measure in above table visual? If possible, could you please also provide your PBIX file to me? You can upload to online file service like OneDrive and share it to me.
I tried but it is still doing the same thing as of before.
It is stil not matching on transaction level cost.
According to the screenshot above, Debits <> Credits, so @v-haibl-msft code of course will result in -1.
@jibran your initial calculated column results in -1 because if you group the subset of data by F_NOM_PERIOD, again Debits <> Credits so you will get -1.
Could you please provide the expected result for each row in the table visual when selecting 1129? Do you want to compare the total DEBIT with the total CREDIT in the same PERIOD for same VEH_ID?