topic Re: DAX measure to calculate the sum of transactions for all users equal or above a threshold percentile in DAX Commands and Tips

DAX measure to calculate the sum of transactions for all users equal or above a threshold percentile

delsaber8 — Sun, 23 Apr 2023 17:08:50 GMT

Hi, I have what I thought was a fairly easy DAX problem, unfortunately I am struggling with it.

I have a user input for a parameter, which takes the input of a percentile. In my data, I have client_IDs associated to any and all of my transactions. I would like to get a measure that calculates the total number of all transactions for clients with a number of transactions at or above the threshold percentile specified by the user. For example, if a user inputs 75 in the what-if parameter, the measure should produce the total number of transactions for all clients equal to or above the 25th percentile as determined by their transactions count. One caveat is that, I need to handle scenarios where there are ties. I want to settle ties arbitrarily. So for example let's say to be in the top 80 percent of clients you need to have at least 2 transactions and to be in the top 70 percent the client needs at least 3 transactions then to get the top 75 we would have to randomly choose to add some of the clients with 2 transactions to our total and to exclude the others. The ones which are kept for the total vs the ones that are not, is not important.

If it were in python it would look like this:

number_of_clients = dataset.groupby('client_id').size().shape[0]

top_count = dataset.groupby('client_id').size().sort_values(ascending = False).cumsum()[:math.ceil(what_if_parameter*number_of_clients)].sum()

Re: DAX measure to calculate the sum of transactions for all users equal or above a threshold percentile

Greg_Deckler — Sun, 23 Apr 2023 19:41:22 GMT

delsaber8 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: DAX measure to calculate the sum of transactions for all users equal or above a threshold percentile

delsaber8 — Sun, 23 Apr 2023 22:06:11 GMT

Hi Greg_Deckler the data should have a similar form to that shown below:

What I need to do is perform a grouping by each client_ID and count all of their transactions. I then need to order the clients beginning with those that have the most transactions all the way down to the ones that have the least. After this, I need to get a total count of transactions belonging to clients in the upper X percentile, where X is specified by the user. So if the user specifes 75, that means they want to get the total count of transactions for the top 75% of clients. But I need to be sure that the sort order does not result in ties. That is to say, if the top 70% have at least 3 transactions and the top 80% have at least 2 transactions, then to get the top 75% I would include some clients which have only 2 transactions, in the top 75% of clients. So in summary, the output is a single number, which is the total count of transactions corresponding to a group of high volume clients.

Transaction ID	Client ID	other info
12345	2345-1	....
12346	0X09-9	....
12347	2345-1	....
12348	EL07-2