Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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:
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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@superjam glad it worked out, take a moment to join my YouTube channel where you will find some interesting Power BI videos. Link below.
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.
@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.
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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@superjam add another measure and use this one for visualization:
Step Sales2 =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( Steps, Steps[Sales] ),
"@Sales", [Step Sales]
),
[@Sales]
)
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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@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:
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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@superjam provide a sample data in a table formate here
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |