How to get the value at 75th percent based on multiple measures
Can someone please help me create a measure to get a specific value based on multiple measures? In the table below based on my Index, I need to get the equivalent Rolling30D with SI value sitting at the 75th percentile. For the sample data below, 75% of 41 would be 31 so I need to be able to retrieve the value 124. I tried creating a measure myself which I've called Trigger Line however it gives me multiple rows, I just need a single number. *Trigger Line = VAR _k = 0.25 VAR SUMMARY = FILTER(ADDCOLUMNS( SUMMARIZE('Date','Date'[Date]), "'Rank'[@value]",[* Rolling30D with SI],"'Rank'[Rank2]",[*Index]), NOT ISBLANK('Rank'[Rank2])) RETURN PERCENTILEX.INC(SUMMARY, 'Rank'[@value], _k) In the table above, the trigger line should only show the value of 124 for all the rows. How can I achieve this?
DAX measure to calculate the sum of transactions for all users equal or above a threshold percentile
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()
PERCENTILE.EXC Bins based on columns
I currently have Store LY Sales TY Sales 1 $298.66 $224.47 1 $67.48 $0 2 $226.24 ($79.99) 2 ($804.98) $110 2 $0 $180 and so on (161 Stores total; 62,318 rows total) I would like to have buckets for the total of each column (Top = Greater than 75 percentile; MID = between 75th and 25th; BOTTOM = Less than 25th Percentile) that I can use to filter visuals This would allow me to compare sales,cost, inventory and gross margin between Top, Mid, and Bottom Stores based on their total sum for each column with their Percentile buckets in mind I thought getting the percentile and then doing a custom column based on that value would get me what I want BUT Measure = PERCENTILE.EXC(Table[LY Sales],.75) returns 204 which is a problem when each of the TOP stores in Table[LY Sales] are all $371,741.45+ Additionally I would like a card filter that shows the DistinctCount of Stores that are in each of these buckets.
PercentileX.INC calculate percentile based on columns in table visual
Hello. Similar to how measures change values based on columns selected in a table visual, I was wondering if theres a way to not specify a particular column in the percentilex.inc function, and have the dimension over which the percentiles are calculated to be based on the columns present in a table visual? This avoids having to create multiple measures that are very similar, except for the difference in column. Greatly appreciate your help, thanks!
