Cumulative ascending repeat with change in column

[AudreyWen]

Hi,

I don't know how to create an formula in PowerBI that makes ascending cumulatives that repeats with every change in column "Name".

Example: (Als = IF function)

In this example is the calculated column "cumulative visitors".

This shows that "Ans" had 7 visitors in total on 5-9-2019 (4+3). The cumulative numbers has to start with every change in name.

I have tried;

But this gives only the total cumulative number for a name. The same result as for column "SomAls" with calculation:

SomAls = CALCULATE(SUM(Blad1[Aantal bezoekers]);ALLEXCEPT(BLAD1;Blad1[Naam])).

So this doesn't give the right numbers that i want. 

I have no idea how i can create the same calculated column in PowerBI.

I hope someone knows how i can do this.

Thanks in advance!

[deepu2108]

Use the below Formula.

CumSum = CALCULATE(SUM('Table'[Total Visitors]),ALLEXCEPT('Table','Table'[Name]),FILTER('Table','Table'[Name]<=EARLIER('Table'[Name]) && 'Table'[Date]<=EARLIER('Table'[Date])))

[AudreyWen]

---

@deepu2108 wrote:

Use the below Formula.

CumSum = CALCULATE(SUM('Table'[Total Visitors]),ALLEXCEPT('Table','Table'[Name]),FILTER('Table','Table'[Name]<=EARLIER('Table'[Name]) && 'Table'[Date]<=EARLIER('Table'[Date])))

---

Hi, Thanks for your reply!

I have tried the DAX that you have given me but I still get the wrong numbers:

In the first range it starts goed but from "Jan" it goes wrong. The numbers has to be the same as in column "Cumulative visitors".

Can you please help me again with this? 🙂 

---

[kentyler]

see my reply below

[kentyler]

Pardon, I think I identified the wrong pattern in my previous post. Here is some sample code from an online article

A similar technique can show running totals over different attributes and dimensions. In the demo database, customers are clustered into different categories, based on purchase volume: Platinum, Gold and Silver. What if the user wants a running total of sales amounts for their top tier customers, starting at the top with the Platinum category? The running total pattern is a useful technique here, too.
The goal is to obtain the following report: :

The DAX code for RT Sales Customer Class uses the very same pattern as for the running total described earlier:

1 2 3 4 5 6 7 8

RT Sales Customer Class := VAR CurrentCustomerClass = SELECTEDVALUE ( Customer[Customer Class Number] ) RETURN     CALCULATE (         [Sales Amount],         Customer[Customer Class Number] <= CurrentCustomerClass,         ALL ( Customer[Customer Class] )     )

showing how to get a cumulative total for a value that changes (in this case "customer class"). The article provides a lot of examples and may help you see the trick you need to do

https://www.sqlbi.com/articles/computing-running-totals-in-dax/

[AudreyWen]

Hi, Thanks for you reply!!

With this i only get cumulative totals but that is not what i mean. I want a column that shows all the cumulatives per row.

I hope you still want to help me with this 🙂

[deepu2108 · Accepted solution]

Hi,

Calcum = CALCULATE(SUM('Table'[Total Visitors]),ALLEXCEPT('Table','Table'[Name],'Table'[Date],'Table'[Index]),FILTER('Table','Table'[Name]<=EARLIER('Table'[Name]) && 'Table'[Date]<=EARLIER('Table'[Date]) && 'Table'[Index]=EARLIER('Table'[Index])))

Hope this helps.

Regards,

Sandeep M

[AudreyWen]

Many thanks!!! 🙂 this works, i'm very happy!