Cumulative Total and Percentage using PowerPivot

[melq64]

Hi,

I created two functions for cumulative total and percentage:

CumulativeTotalDelivered:=CALCULATE([TotalItemsDelivered],FILTER(ALL(dCalenderDel),dCalenderDel[Date]<=MAX(dCalenderDel[Date])),VALUES(dCalenderDel[Date]))


%CumulativeTotalDelivered:=DIVIDE([CumulativeTotalDelivered],CALCULATE([CumulativeTotalDelivered],ALLEXCEPT(dCalenderDel,dCalenderDel[Year])))

The output result (via pivot table) is not quite right as seen in image screenshot below:

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

I need the cumulative % result to add up to 100% from LEFT to RIGHT and not top to bottom as shown in image above. Hope that makes sense.

Please let me know if you need further clarification.

Appreciate any help.


Thanks

 

[VBA Geek]

Take a look at the ALLSELECTED function

 

[melq64]

Thanks for the suggestion VBA Geek but after changing formula to include ALLSELECTED, the values in pivot all changed to 100%

Here's the actual dataset that my functions are referencing so hope that will provide you with more context.

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

FYI here's the function for TotalItemsDelivered mentioned earlier:

TotalItemsDelivered:=CALCULATE(COUNTROWS('Cumulative dataset'),FILTER('Cumulative dataset','Cumulative dataset'[Delivery]<>BLANK()))

Also note that for [Delivery] column, I created at separate calendar table named 'dCalendarDel'

Please let me know if you need further details.

Again I appreciate any help you can provide.


Thanks

 

[melq64]

Sorry forgot to mention in the dataset file, the highlighted columns WERE calculated columns with formulas but I've replaced them with values only.

 

[VBA Geek]

Check if this file does what you want...

https://1drv.ms/x/s!AiiWkkwHZChHjwwJPlt_km7L279f

 

[melq64]

Yes that is PERFECT Please share the correct dax function

 

[melq64]

Hi, just wanting to follow up if you managed to create correct DAX formula for the issue? Hope to hear from you soon.

Thanks

 

[melq64]

My apologies. Please ignore my last message as I just found the dax function within attachment.

Thank you again for your help with this. Truly appreciate it