Calculating number of days it takes to reach 80% of total

DAX DAX Calculations
1

I’ve attached an excel file with a sample data set.

This excel has 3 columns:

Lag days: the number of days passing between when a lead enters the system and when they make a sale
Count: a count of leads for each number of lag days
% of total: % of total for the count column.

I’m trying to locate the earliest lag day in which the cumulative % of total is 80% or more. That is, 80% of leads fall into this range of lag days between 0 and X.

In the dataset example, scroll down to lag day 160, highlighted green. On this day, the cumulative % of total reaches 80%. 160 is the result I’m looking for in this example.

I believe I need to first find a cumulative % of total as you move from 0 to higher lag days. Then isolate the earliest lag day on which that cumulative total reaches 80%. I’ve attached a pbix file with the base measures (count, cumulative total count, cumulative total % of total, etc.). Now I just need to figure out how to isolate day 160 as the result, which is the first day that the cumulative total is 80%.

Hope this makes sense! Thanks in advance.
Example Data.csv (5.6 KB)
Example Data PBIX.pbix (31.4 KB)

2

Bumping this post for more visibility.

3

Hi @pete.langlois,

This seems the same as How to locate the day on which cumulative total is greater or equal to 80%, solved by Rajesh.

If you want the result in a card, (without the need of the context within a table), you can use:

DayReachEightyPerctII = 
 CALCULATE(MIN('Example Data'[Lag Days]), FILTER(ALL('Example Data'[Lag Days]), [Cumulative % of Total] >= 0.80))

Kind regards, DS

4

Hi @pete.langlois, due to inactivity from your end, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.