How to calculate amount per completed sales step

[superjam]

I have the following situation and could not figure out how to resolve it:

There is a sales table, containig (amongst other columns) the dates on which several steps in a sales process has been completed. If this dates contain data, it means that the step has been completed. If not, then it is pending. Example extract below:

So, for example, sale 1 is currently on step 4 (because step 3 is completed).

Problem: I need to show how much money is on which completed step. For example, "Sale 1" has 1000 on step 3, "Sale 2" has 1500 on step 5 and "Sale 3" has 1100 on step 1

So, is there a way to do this? How may a formula look like, that calculates 

1. which is the step I need to consider for each line (or sale)

2. aggregation for each step, that calculates the sum of money per step completed.  

Hopefully you may help me out with this one.

Thank you and regards.

[parry2k · Accepted solution]

@superjam the best is to unpivot your table and then add following measure:

transform data -> select sales and amount columns -> right click -> Unpivot other columns 

It will add two columns, attribute and value, renames these to step and date, close and apply

add measure:

Step Sales = 
CALCULATE ( 
    SUM ( Steps[Amount] ),  
    KEEPFILTERS (
    TOPN ( 
        1,
        FILTER ( 
            ALLSELECTED ( Steps ), Steps[Sales] = MAX ( Steps[Sales] ) ),   
            CALCULATE ( MAX ( Steps[Step] ) ) , 
            DESC 
        ) 
    ) 
)

to visualize, add sales, step and above measure and you will get this:

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[parry2k]

@superjam glad it worked out, take a moment to join my YouTube channel where you will find some interesting Power BI videos. Link below.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[superjam]

@parry2k ,

just subscribed your channel. I am sure I will learn a lot with you.

And regarding the previous mentioned problem, I am investing hours in trying to understand the measures you did (I think I am half way through now!)

If I am not abusing your kindness, how would you calculate the count of sales that are on each step? So I could make a barchart that displays how many sales completed step 1, how many completed step 2, etc.

Thank you.

[parry2k · Accepted solution]

@superjam [Step Sales] is the existing measure that I gave you before, if you have added that as a different name use that measure name.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[superjam]

@parry2k , you really ROCK!! (And I certainly don´t; sorry).

Thanks a lot and best regards.

[parry2k]

@superjam add another measure and use this one for visualization:

Step Sales2 = 
 SUMX ( 
     ADDCOLUMNS ( 
         SUMMARIZE ( Steps, Steps[Sales] ),     
         "@Sales", [Step Sales] 
        ), 
        [@Sales] 
    )

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[superjam]

Thank you, again, Parry2k.

It did not work (error on the visual). I am still trying to understand the measure you suggest, but maybe there is an error on the line here: 

"@Sales" , [Step Sales]

Thank you and regards.

[parry2k · Accepted solution]

@superjam the best is to unpivot your table and then add following measure:

transform data -> select sales and amount columns -> right click -> Unpivot other columns 

It will add two columns, attribute and value, renames these to step and date, close and apply

add measure:

Step Sales = 
CALCULATE ( 
    SUM ( Steps[Amount] ),  
    KEEPFILTERS (
    TOPN ( 
        1,
        FILTER ( 
            ALLSELECTED ( Steps ), Steps[Sales] = MAX ( Steps[Sales] ) ),   
            CALCULATE ( MAX ( Steps[Step] ) ) , 
            DESC 
        ) 
    ) 
)

to visualize, add sales, step and above measure and you will get this:

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[superjam]

Hello, Parry2K

Thank you very much for your kind and fast help.

I just did exactly by your suggestion and it worked fine! Great.

Now I wanted to get one step further and visualize a bar chart that summarizes the sales ammount by step. (Step on X axis and sales on Y; so that I could see for all sales, how much money is on each step). But this did not work at all. It only shows 1 bar, on one step, that is the first one. What am I missing?

Thank you again and regards.

[smpa01]

@superjam  provide a sample data in a table formate here