Cumulative line (Whale Curve)

[Anonymous]

Hi,

I'm trying to make a cumulative line based on sales to each customer.

Currently I have sales and customers in a graph but the amount does not add up (blue line). How to make it like shown(in red) in the picture?

Thanks in advance

[Baskar]

Try this 

Var _date = max(date column)

Measure=

    calculate(sum(sales),keepfilters( All(date column) <= _date) )

replace your date column 

[Anonymous]

Hi Baskar,

Doesn't seem to work:

1. OmzWhale = 
VAR var_Date = max(Dates[Date])
Measure = CALCULATE(SUM(TB[Amount]),KEEPFILTERS(ALL(Dates[Date]) <= var_Date))

This gives error: The syntax for Measure is incorrect.

1. OmzWhale = 
VAR var_Date = max(Dates[Date])
Return CALCULATE(SUM(TB[Amount]),KEEPFILTERS(ALL(Dates[Date]) <= var_Date))

This gives "The true/false expression does not specify a column."

[richbenmintz]

Hi @Anonymous,

please try

OmzWhale = 
VAR var_Date = max(Dates[Date])
Measure = CALCULATE(SUM(TB[Amount]),FILTER(ALL('Dates'),Dates[Date]) <= var_Date))

Hope this Helps,
Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up! 

[Anonymous]

Hi, still gives me an error on the "Measure =" part.
Fyi it does not recognize the last part where you use var_Date

[richbenmintz]

hi @Anonymous,

sorry,

missed the return and made a pasting error,

OmzWhale = 
VAR var_Date = max(Dates[Date])
return
CALCULATE(SUM(TB[Amount]),FILTER(ALL('Dates'),Dates[Date]) <= var_Date))

[Anonymous]

This returns the following error:
A function "FILTER" has been used in a true/false expression that is used as a table filter expression. This is not allowed.

Thanks for your help so far @richbenmintz 

[Anonymous]

It works if I use:

1. OmzWhale = 
VAR var_Date = max(Dates[Date])
Return CALCULATE(SUM(TB[Amount]),Dates[Date] <= var_Date))

But the line does not look different.
The X axis in the graph is not date related FYI it's all the customers sorted by biggest amount to lowest. 

---

Hi @JVDS ,

Again some bracketing issues

---

This works, yes, but same as above. The graph line does not change.

[richbenmintz]

Hi @Anonymous,

please provide sample data and expected output.

Thanks,

[Anonymous]

Hi,

Ok the table where we are getting things from is a transaction table, with every line being a transaction.

We are using the filtered amount, not normal amount 
Amount Filtered (Amount = 0 if not class 2 in other table)

Expected result is shown in the picture..

Amount	Customer	Date	Amount Filtered	Class
1001	Cus1	01-01-20	1001	Class 2
200	Cus2	20-01-20	200	Class 2
500	Cus3	13-03-20	0	Class 1
300	Cus2	13-03-20	0	Class 3
200	Cus1	15-02-20	200	Class 2

The solution you provided me works on date, I don't need it to work on date. I need it to work on customers

So from highest to lowest amount the line should go:

Customer 1 + customer 2,
Customer 1 and 2 + customer 3,
Customer 1, 2 and 3 + customer 4,
etc

This, but the X axis is NOT dates but CUSTOMERS

[richbenmintz]

Hi @Anonymous ,

Please try the following Measures as i think they provide what you are asking for

OmzWhaleRank = 
    var ranker = RANKX(all('TB'), [Amount Filtered ], SUM(TB[Amount Filtered ]), desc, Skip)
return
    ranker

OmzWhale = 
    var ranker = RANKX(all('TB'), [Amount Filtered ], SUM(TB[Amount Filtered ]), desc, Skip)
    //create the cummulative sum of the Customers from minnow to Whale
    var cum_val = CALCULATE(sum(TB[Amount Filtered ]), FILTER(all('TB'), ranker <= [OmzWhaleRank]))
return
    cum_val

Hope this Helps,
Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

Hey Richard,

Almost there I think 🙂

It just needs to have 1 fix.

Right now it cumulates from lowest cost to highest. But I want it to start with the highest and then add all the others to that.
light blue line is what I have now with your latest measure:

This is the result I'm looking for:

[richbenmintz]

Hi @Anonymous,

All you had to do was change the sort order of the ranking function.

Hope this Helps,
Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous · Accepted solution]

Ended up using:
https://www.youtube.com/watch?v=rlUBO5qoKow
And got it working

[richbenmintz]

Hi @Anonymous ,

Again some bracketing issues

OmzWhale = 
VAR var_Date = max(Dates[Date])
return
CALCULATE(SUM(TB[Amount]),FILTER(ALL('Dates'),Dates[Date] <= var_Date))