topic Re: DAX Cumulative Total and Cumulative % in DAX Commands and Tips

DAX Cumulative Total and Cumulative %

Anonymous — Wed, 23 Oct 2019 08:21:15 GMT

I have this cumulative DAX function that is supposed to show the number of days it takes for items to be delivered.

But the output doesn't look right i.e. I expect the cumulative % to be higher as per screenshot above. The expected result should be 70%-80% on Day 3

CumulativeTotalDelivered:=IF(MIN(dCalenderDel[Date])>CALCULATE(MAX('DistProfile v1-3'[Delivery]),ALL('DistProfile v1-3')),BLANK(),CALCULATE(COUNTROWS('DistProfile v1-3'),FILTER(ALL(dCalenderDel),dCalenderDel[Date]<=MAX(dCalenderDel[Date]))))

%CumulativeTotalDelivered:=DIVIDE([CumulativeTotalDelivered],CALCULATE([CumulativeTotalDelivered],ALLSELECTED('DistProfile v1-3'[Del Days])))

Note: [Delivery] column contains delivery dates

Here is sample file

https://drive.google.com/file/d/1cE2Ui4sBlHUwjFExBvcii29lL5o8R2mf/view?usp=sharing

Re: DAX Cumulative Total and Cumulative %

Anonymous — Thu, 24 Oct 2019 09:48:51 GMT

Nathaniel_C sorry to tag you but you're the only one I could think of since you assisted me recently in my last DAX issue

https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-SUM-Positive-Numbers-Only-From-Multiple-Columns/td-p/825555

Any thoughts on this issue I have with Cumulative total and %? Appreciate any help.

Thanks.

Re: DAX Cumulative Total and Cumulative %

Nathaniel_C — Fri, 25 Oct 2019 00:36:55 GMT

Anonymous ,

Thanks for tag, I enjoy working on fun stuff, as long as I can get a pbix, or copy and paste the data into Power Query!

Below is the crux of the code. Hopefully I understood your issue, and what you were looking for as an output. You could fine tune this by using the date time as well.

Here is my pbix file:Delivery percentage
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Cumulative % =
VAR _currentDay =
    MAX ( Del[No of Days to Deliver] )
VAR _calc =
    CALCULATE ( [Count All], Del[No of Days To Deliver] <= _currentDay )
RETURN
    _calc

along with this.

Count All no really = CALCULATE(COUNT(Del[No of Days To Deliver]),ALL(Del))

The interesting part is that we use the count when we are filtering against the number of days it takes to deliver, but we use Count ALL, no really when we really want the total to not filter against the days, as it is the total.

Re: DAX Cumulative Total and Cumulative %

Anonymous — Sun, 27 Oct 2019 09:45:07 GMT

Nathaniel_C thank you but unfortunately when applying this to my data model, it doesn't work as intended. It looks correct from your end but not mine. Note: I'm using PowerPivot/PowerQuery in Excel, not PowerBi app so I couldn't open your pbix file.

1. in comparison to my existing DAX measure, the output is not the same as per screenshot below i.e. cumulative total is not the same

2. cumulative % - shouldn't this be 100%?

I've manually double/triple checked my existing DAX measure and I'm confident it works but its just the cumulative % that is not correct i.e. it's showing % delivered by day instead of % cumulative delivered by day

Just to clarify how I use this measure(my existing DAX measure) in pivot table, here is screenshot (by Month) below, I know the measure is correct when I manually sum the % figures of first 3 days for Oct, the sum is 76% delivered on Day 3 which is correct. However, the correct DAX formula should output 76% in Day 3 instead of 36%

Re: DAX Cumulative Total and Cumulative %

Nathaniel_C — Fri, 25 Oct 2019 23:30:18 GMT

Hi Anonymous ,

So not sure that I am reading this right, but do you average the weeks for the month?

Re: DAX Cumulative Total and Cumulative %

Anonymous — Fri, 25 Oct 2019 23:47:59 GMT

No average, I did a manual SUM as a check for my existing measure. The [Delivery] column is a date field which I have linked to a calendar table which I use to break down the dates by day, week, month, etc

Re: DAX Cumulative Total and Cumulative %

Nathaniel_C — Sat, 26 Oct 2019 01:15:30 GMT

Hi Anonymous ,

So, here is the EXCEL File. Cumulative Hopefully you can look at that and it will be a tool to figure it out. Or maybe you can duplicate the measures, using your own table. This table is the one that you posted for me.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: DAX Cumulative Total and Cumulative %

Anonymous — Sun, 27 Oct 2019 09:41:46 GMT

Hi Nathaniel_C it still didn't work when I transfered your measure to my data model and I know the reason why. Your measure works perfectly fine but NOT when DATE filters are applied. If you recall my initial measure, it included DATE filter in it. Are you able to edit your measure to include DATE filter in it?

Here is link to your original Excel file including full dataset that I have added to it. I also added a linked calendar table.

Re: DAX Cumulative Total and Cumulative %

Nathaniel_C — Mon, 28 Oct 2019 22:41:08 GMT

Hi jdbuchanan71 ?

Re: DAX Cumulative Total and Cumulative %

jdbuchanan71 — Tue, 29 Oct 2019 00:30:10 GMT

Anonymous

I believe this is what you are looking for?

I have attached the updated .xlsx file, my measures are on the RawData table.

Re: DAX Cumulative Total and Cumulative %

Anonymous — Tue, 29 Oct 2019 09:04:07 GMT

jdbuchanan71 thank you very much for this, almost perfect, just 1 small tweak. How do I include 0 in the countrows measure

Count:=CALCULATE(COUNTROWS('DistProfile v1-3'), KEEPFILTERS('DistProfile v1-3'),'DistProfile v1-3'[Del Days]<>BLANK())

Currently its not counting values in [Del Days] where value = 0

Re: DAX Cumulative Total and Cumulative %

jdbuchanan71 — Tue, 29 Oct 2019 13:10:30 GMT

Just change the measure to this

Count:=COUNTROWS(RawData)