topic Re: Cumulative Total of id count per lead days in DAX Commands and Tips

Cumulative Total of id count per lead days

vikash-jamnadas — Tue, 08 Mar 2022 00:02:17 GMT

Hi, I am trying to get the running total id count per lead to contact days. I am failing miserably - any help wold be appreciated.

thanks

Vikash

Re: Cumulative Total of id count per lead days

MattAllington — Tue, 08 Mar 2022 00:30:38 GMT

Read my article here https://exceleratorbi.com.au/pareto-analysis-in-power-bi/

Re: Cumulative Total of id count per lead days

vikash-jamnadas — Tue, 08 Mar 2022 02:56:53 GMT

Hi MattAllington ,

Thanks for this. Following is what I have done

Cumulative % =

VAR LeadCnt = opportunity[Total Leads] /* this sets the margin for the current product

(the one that is the current column in the column chart) */

VAR TotalLeadAllLead2Contact =

CALCULATE([Total Leads], ALL(opportunity[Lead to Contacted Days])) /* the total margin across all products.

This is needed to turn the absolute margin into a percentage */

RETURN

CALCULATE(

[Total Leads],

FILTER( ALL(opportunity[Lead to Contacted Days]), [Total Leads] >= LeadCnt)

) / TotalLeadAllLead2Contact

I can see that this is working by sorting Total Lead count in desc order and calculating the cumulative based on that. However I want the cumulative based on opportunity[Lead to Contacted Days] in asc order. How will I have to adjust the formular to achive this?

Kind Regards

Vikash

Re: Cumulative Total of id count per lead days

MattAllington — Tue, 08 Mar 2022 06:42:42 GMT

It's been a while. My guess is, change the >= LeadCnt to be <=LeadCnt

Re: Cumulative Total of id count per lead days

vikash-jamnadas — Tue, 08 Mar 2022 20:50:02 GMT

MattAllington I'm afraid this does not work.

Re: Cumulative Total of id count per lead days

v-yanjiang-msft — Thu, 10 Mar 2022 09:12:35 GMT

Hi vikash-jamnadas ,

Try to modify the return part like this:

RETURN CALCULATE ( [Total Leads], FILTER ( ALL ( opportunity ), [Lead to Contacted Days] <= MAX ( [Lead to Contacted Days] ) ) ) / TotalLeadAllLead2Contact

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

Re: Cumulative Total of id count per lead days

v-yanjiang-msft — Wed, 16 Mar 2022 03:02:02 GMT

Hi vikash-jamnadas ,

Have you tried my suggestion? Can it solve your problem?

Best Regards,
Community Support Team _ kalyj