Cumulative Order Count not having correct row and column totals

[leekarensl]

Hey all,

I wonder if you are able to assist. My stakeholder is after a measure that calculates the cumulative count of order_id by campaign and strategy. However while the actual cumulative order count is working, for some reason the row and column totals are not adding up properly and are just taking the last figure. Is there a way that I can get the measure to calculate cumulative count of order-id and also for the totals to be correct? I can't seem to attach the pbix file here but I am attaching screenshot of the output:

The measure is defined as:

cumu_date =

CALCULATE(

    count(data1[ORDER_ID]),

    FILTER(

        ALLSELECTED(data1),

        data1[CONVERSION_DATE] <= max(data1[CONVERSION_DATE])&&

        data1[CAMPAIGN] = max(data1[CAMPAIGN])&&

        data1[STRATEGY] = max(data1[STRATEGY])

    )

)

Thanks in advance for your help.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file and show the expected result very clearly.

[leekarensl]

Thanks for your response. I have shared as part of this chat.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[leekarensl]

Perfect! Thank you so much!

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi, @leekarensl 

I've modeled some data that I hope fits your situation. You can try the following.

Count order = COUNT('Table'[ORDER_ID])

Cumulative Count = 
 Var _cumucount=COUNTX(FILTER(ALL('Table'),[CONVERSION_DATE]<=SELECTEDVALUE('Table'[CONVERSION_DATE])),[Count order])
 RETURN
 IF(HASONEVALUE('Table'[ORDER_ID]),[Count order],_cumucount)

Is this your desired outcome? Please review the attachment.

Best Regards,

Community Support Team _Charlotte

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

[leekarensl]

Hi, thanks for your response. No please see attached test pbix and the dataset that I have linked separately as I can't get it to be part of the shared pbix.

The table on the left is the normal order count and the row and column totals are calculating correctly. However if you look at the table on the right, take for example 12th March, it is giving cumulative order count for Conversion to be 19 when it should be 33. So below screenshot should be the correct output for example for 12th March:

test pbix 

dataset 

Thanks in advance for your help.

[elitesmitpatel]

Please share the PBIX file with Dummy data and Highlight the expected output.

[leekarensl]

Thanks for your reponse. I have shared as part of this chat.

[leekarensl]

Thanks for your response. While I can attach screenshots, there's no option for me to insert the pbix file here.

[elitesmitpatel]

1st upload the file in onedrive or google drive then click on   below highlighted icon and attach the link of your pbix file

[danextian]

Hi @leekarensl 

Why the need the cumulative to sum the cumulative count of each row at the total level? Cumulative count is supposed to be the running balance at a given date. For example if Sep 1- 19 had a unit sale of 1 each, each date would have a cumulative count equivalent to the day of month and  so on Sep 19, the running count would just be 19 and not the sum of all cumulative counts of each date.

[leekarensl]

Because we want to know what the cumulative totals are by campaign (row totals) and strategy (column totals).