topic Need to compare two tables and count number of customers not in balance in DAX Commands and Tips

Need to compare two tables and count number of customers not in balance

MegaOctane1 — Sun, 15 Oct 2023 10:15:44 GMT

I have three tables.

Table 1 CustomerMaster:

CustomerNumber	Customer Name
100	Apple
101	Microsoft

Table 2, Open invoices in ERP:

CustomerNumber	Invoice	Amount
100	3	1000
100	4	2000
101	5	3000

Table 3, Open invoices InvoicePartner:

CustomerNumber	Invoice	Amount
100	3	1000
101	5	3000

Relationship is on KundeNummer in each table.

I have a measure to calculate the difference of these two tables.
Measure is:

Difference = VAR _DIFF_CHECK = [SUM ERP]-[SUM InvoicePartner] Return IF(_DIFF_CHECK<>0, _DIFF_CHECK)

it returns this:

CustomerNumber	Difference
100	2000
101	0

How can i create a card visual/measure to say that number of customers with difference is 1?

i am not able to do a countIF(Difference<>0)

Re: Need to compare two tables and count number of customers not in balance

123abc — Tue, 17 Oct 2023 05:36:01 GMT

To create a card visual or measure that displays the number of customers with differences in the "Difference" measure, you can follow these steps:

Create a new measure that calculates the count of customers with differences:

CountOfCustomersWithDifferences = COUNTROWS(FILTER(CustomerMaster, [Difference] <> 0))

This measure counts the rows in the "CustomerMaster" table where the "Difference" measure is not equal to 0.

Add a card visual to your report.
In the card visual, use the "CountOfCustomersWithDifferences" measure as the value to display.

Now, your card visual will show the number of customers with differences in their invoices. If the "Difference" measure is not equal to 0 for a customer, it will be counted in this card visual. In your example data, it will show "1" because there is one customer with a difference (CustomerNumber 100).

Re: Need to compare two tables and count number of customers not in balance

Dangar332 — Tue, 17 Oct 2023 07:17:26 GMT

Hi, MegaOctane1

try below with same relationship which you mention
make measure in customer master table

diff =

var a =SUM('invoice partner'[Amount])

var b = SUM('open invoice'[Amount])

return

ABS(a-b)

and use customer number in table visuals from your master table