cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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
6 REPLIES 6
Super User

Hi,
try to put this measure in a matrix

Measure =
SUMX(
'DataTable'
,CALCULATE(
DISTINCTCOUNT('DataTable'[customer_id])
,'DataTable'[  qty] > 0
)
)

Regards,

Luca D'Elicio

Luca D'Elicio

Regular Visitor

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.

Super User

Use https://wetransfer.com/
Thank you,

Luca D'Elicio

Regular Visitor

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

Super User

Use https://wetransfer.com/
Thank you

Luca D'Elicio

Super User

Hi,

Thank you.

Luca D'Elicio

Luca D'Elicio

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.