Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
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.
Many Thanks
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 )
Best Regards,
Herbert
Hi,
I tried but it is still doing the same thing as of before.
It is stil not matching on transaction level cost.
@jibran wrote:Hi,
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.
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.
Best Regards,
Herbert
Can I have your email address so I can share with you power BI report?
Many Thanks
Hi Herbert,
I did send you the pbix file on your email address, did you recieved?
Many Thanks
Jibran Ishtiaq
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?
Best Regards,
Herbert
Hi Herbert,
Actullay the desired result is that to match total credit with total debit based on vehicle ID (Which has already been achieved with your help). But I want to match the Credit cost with debit cost at line level. So in case of 1129 "60.00" matches with debit side "60.00" and if matches it shows 0.
If this can not be achieved then can I put filter on equal2 formula (i.e. show me "0" or shown me "-1" only)
Many Thanks
Jibran Ishtiaq
Could you please give me a sample in your PBIX which should return 0? In 1129, I do not see any equals as below.
Best Regards,
Herbert
Hi,
Thanks I'll try this now, just on a quick note how can I apply filter on 0,-1. That if I just want to see -1.
Many Thanks
Jibran
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |