Finding distinct count within a years worth of transactions split by months

[kponline]

Hello, I have this dataset of sales transactions spanning 3 months.

What I want is to show the data in a table visual the calculation of the distinct count for each month and the total when I select the year as 2023 using a selector.  
For an eg: for the months July/Aug/Sep, customer 100101 made 3 purchases (one for each purchases). I use the distinctcount function to find the number of distinctcount of customers for each month. Lets call this measure, Uniq. Cust. Given this, for the month of July  distinctcount(table[customer_id]) gives me a value of 2. On the same note for Aug and September it gives me a value of 1 and 2 respectively when I display the month along with the measure Uniq. Cust. Now when it display this as part of the total at the bottom of the table visual or the card visual, total becomes 3 for the whole 3 months of data. What was happenning it only takes the distinct customers id's across all 3 months and total as 3, instead of 5. Can you guys kindly help me calculate this measure so it shows 5 as the total when displaying on a  table or a card visual's total? 

sale_date	customer_id	sale_id	qty	item_id
2023-07-02	100101	1236899	1	A1000
2023-07-23	100102	2342879	3	C2000
2023-08-14	100101	2399111	1	R8822
2023-09-30	100101	3270000	3	CL12
2023-09-18	100110	0811123	8	R8822

Month	Unique Customers	Year
July	2	2023
Auguast	1	2023
Sep	2	2023
Total	3
Total Should be 5 but distinctcount gives me 3

[lucadelicio]

Hi,
try to put this measure in a matrix

Measure =

SUMX(

    'DataTable'

    ,CALCULATE(

        DISTINCTCOUNT('DataTable'[customer_id])

        ,'DataTable'[  qty] > 0

    )

)

IF I HELP YOU TO RESOLVE YOUR PROBLEM PLEASE MARK IT AS A SOLUTION

Regards,

Luca D'Elicio

[kponline]

Thank you for your reply. For this specific dataset, your formula perfectly works. In my original powerbi report it involves more relationships between the this transaction table, the location and customer table. If I apply the formula without making any changes to it, the distinct customer numbers for each month far exceeds the actual number of unique customers but the still the total is correct. Any idea what else I could include in the formula to be accurate? I would accept this as the solution if no one else reply to the post from this point on.

[lucadelicio]

Use https://wetransfer.com/
to upload the pbix file with the correct relationhips and i will help you!
Thank you,

[kponline]

Thank you Luca. I updated the question with more info. Not sure how to upload an Excel file. I can upload pics etc. though..

[lucadelicio]

Use https://wetransfer.com/
to upload the pbix file.
Thank you

[lucadelicio]

Hi,

please share excel file with example data and attended result in a image to help you.

Thank you.

Luca D'Elicio