Count number of transactions for users with more than one transaction only

[ahmed9s]

Hello,

I am very new to the community and new to PowerBI as well. I have found the solutions here very useful. I stuck with a calculation that i can easily do in Excel but having difficulty understanding the right approach in DAX.

I have one table with a column 'userID' and another column 'order total' for each transaction; this table also has a 'date & time' column. I needed to return a total number of transactions for only those users who have more than one transation.

As such that for following data:

User   | order total

User1 | 12

User1 | 12

User2 | 10

User3 | 1

User3 | 1

User3 | 1

User4 | 1

User5 | 2

The result should be '5' as user1 and user3 have more than one transaction each and a total of 5. Ignoring all who have only 1 transactions.

Would would be right approach to achieve this and can someone help with the dax formula.

Thanks,

Ahmed 

[parry2k]

@ahmed9s there are few ways to do this

try this measure

Measure 3 = 
CALCULATE ( 
    COUNTROWS ( 'Table' ), 
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[User], "__cnt", COUNTROWS ( 'Table' ) ),
        [__cnt] > 1
    )
) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[ahmed9s]

@parry2k . Sorry, first part of my previous message got deleted.
Thank you for your solution. I had came to a similar solution as follows:

CALCULATE(SUM(orders[Count Transactions]),

FILTER(

SUMMARIZE(orders,orders[userID],

"Total Purchases", [Count Users]),

[Total Purchases]>=2))

[parry2k]

@ahmed9s so I'm confused, it is working or not working?

[ahmed9s]

@parry2k No. It is not working. the total is correct. But when i combine it with date, or users in the table there are big missing numbers as shown in the pic i attached.

[ahmed9s]

@parry2k Is there another approach to solve this?

[ahmed9s]

Grand total is correct. But the underlying groups does not add up to the total.

[Icey]

Hi @ahmed9s ,

Grand total is correct. But the underlying groups does not add up to the total.

---

What does it mean? What should the result you want be?

Please share us with more details. It is best to create some sample data with the same structure as your real data.

Best Regards,

Icey

[parry2k]

@ahmed9s solution attached, look at page 4 in attached and a table called Hour Group ignore everything else in the file.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[ahmed9s]

@parry2k Thank you very much!

[ahmed9s]

@parry2k Thanks again for the help. Implementing the solution on my model has clarified certain concepts for me.

I am having issue when i filter the visual with a slicer for a specific month. Without the filter for a specific month it works perfect. but when i select a month the totals go quite a bit off. Is there is a solution for this? 

[parry2k]

@ahmed9s you have to be more clear what is off, provide numbers otherwise it is not clear, I requested you to change the measure but not sure if you had a chance to do it or not, here is revised pbix file.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[ahmed9s]

@parry2k Thank you for the link.

Ok, i played with the sample data. I want to be able to count only the users with multiple orders within a given month. What your suggested measures are doing is counting users who have multiple orders in the whole data set. I have updated the sample data and created 'desired results' summary along with a summary showing 'current results' which are given if we use the following measure for 'Orders Count by Users':

Orders Count by Users = CALCULATE ( [Total Orders], ALLEXCEPT ( orders, orders[userID] ) )

This is the sample data: https://drive.google.com/file/d/1VwKGuZ4xeOTf30hWvOOuYfWKg9ObN5Al/view?usp=sharing

I am sorry for not being able to describe my desired results in the beginning. I am gaining clearer understanding of the underlying issue.

[ahmed9s]

Hello everyone and @parry2k. I am still curious to undertsand what could be one approach to achieve the desired results.

[ahmed9s]

@Icey, Thank you for the suggestion. I worked a bit with a sample data and that highlighted for me the issue.

After getting the count of transactions of users having only multiple transactions, i need to group/segment them by 'group of hours' the orders were placed.

The above formula by @parry2k works nicely for 'group of hours' within which above logic applies but does not count user transactions who have only 1 transaction within a specifc 'group of hours'.

I am not sure if i am able to express properly. I did the same calculation on Excel and my result for sample should be as follows:

		Orders by users ordered more than once	Order by users with only one order	Total Orders
12 to 4 AM		3	0	3
12 to 4 PM		7	36	43
5 to 8 PM		1	0	1
9 to 12 AM		1	1	2
Total		12	37	49

However in a table in Power BI the results are following:

It is not counting 4 transactions becasue they are occuring only once for a given user within a group. I cannot find the option to attach a sample data fil.e I will just copy the data relvant to the required mesaure below.

Data:

UserID	Date		Pur	Hours	Hour Group
96866595	11/1/2019 14:17		5	14	12 to 4 PM
96866595	11/1/2019 14:30		5	14	12 to 4 PM
96866595	11/1/2019 14:31		5	14	12 to 4 PM
96866595	11/1/2019 17:22		5	17	5 to 8 PM
96866595	11/1/2019 21:36		5	21	9 to 12 AM
148781884	11/1/2019 0:48		4	0	12 to 4 AM
148781884	11/1/2019 12:36		4	12	12 to 4 PM
148781884	11/2/2019 13:13		4	13	12 to 4 PM
148781884	11/1/2019 14:11		4	14	12 to 4 PM
57544002	11/1/2019 0:47		3	0	12 to 4 AM
57544002	11/1/2019 1:47		3	1	12 to 4 AM
57544002	11/1/2019 12:30		3	12	12 to 4 PM

[ahmed9s]

@parry2k

They both work fine at the aggregate level. But numbers doesn't look right when i group them within a bins of hours. I added calculated column to get hour from the 'date & time' field in the same table. And created hour bins by creating groups.

As you can see in the following screen:

The total here is correct as per my data but the sum of all the underlying values doesn't add up to the total there 3,257 transactions. This method of grouping worked perfectly when i use simple countrows without condition. 

I assumed that my approach was not correct and somehow it is only calculating one transaction per user and that's the reason of the difference.

Can you please help understand where I am wrong in this? Is it the grouping i need to do another way or to get this kind of grouping we need to calculate the transactions with another approach?

Thank you for the assiatnce.

Ahmed

[rubinboer]

Your Result = COUNTROWS(FILTER(YourTable,Yourtable[YourColumn] > 1))

This counts the total rows where the value is more than 1