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'm using Power BI connecting to Salesforce to access the Opportunity Field History object (table). I'd like to end up with each stage an opportunity hits, in order, on a single row so I can then build a flow diagram to illustrate pull through and fall out.
Solution Example:
OppID | Stage1 | Stage2 | Stage3 | Stage4 | Stage5 |
ABC123 | Prospecting | Qualifying | Estimating | Proposing | Closing |
The data table is set up to record changes in the stage name so the data comes across as follows:
OppID | Date | OldValue | NewValue |
ABC123 | 1/1/2020 | Prospecting | Qualifying |
ABC123 | 1/2/2020 | Qualifying | Estimating |
ABC123 | 1/3/2020 | Estimating | Proposing |
ABC123 | 1/4/2020 | Proposing | Closing |
Any help with this would be super helpful... i've been trying to figure this out for some time. Thanks in advance!
Hi @jdonovan ,
You can refer to the following DAX:
Column =
RANKX (
FILTER ( 'Table', 'Table'[OppID] = EARLIER ( 'Table'[OppID] ) ),
'Table'[Date],
,
ASC,
DENSE
)
Table 2 =
SUMMARIZE (
'Table',
"ID", DISTINCT ( 'Table'[OppID] ),
"Stage 1", CALCULATE ( SELECTEDVALUE ( 'Table'[OldValue] ), 'Table'[Column] = 1 ),
"Stage 2", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 1 ),
"Stage 3", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 2 ),
"Stage 4", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 3 ),
"Stage 5", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 4 )
)
Here is the result of my test.
Hi @jdonovan ,
You can refer to the following DAX:
Column =
RANKX (
FILTER ( 'Table', 'Table'[OppID] = EARLIER ( 'Table'[OppID] ) ),
'Table'[Date],
,
ASC,
DENSE
)
Table 2 =
SUMMARIZE (
'Table',
"ID", DISTINCT ( 'Table'[OppID] ),
"Stage 1", CALCULATE ( SELECTEDVALUE ( 'Table'[OldValue] ), 'Table'[Column] = 1 ),
"Stage 2", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 1 ),
"Stage 3", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 2 ),
"Stage 4", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 3 ),
"Stage 5", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 4 )
)
Here is the result of my test.
Hi,
I think we can carry out some transformations in the Query Editor to solve this. Are Power Query trnsformations allowed when you directly soruce data from SalesForce?
Hi @jdonovan ,
You could refer to the following DAX:
Column =
RANKX (
FILTER ( 'Table', 'Table'[OppID] = EARLIER ( 'Table'[OppID] ) ),
'Table'[Date],
,
ASC,
DENSE
)
Table 2 =
SUMMARIZE (
'Table',
"ID", DISTINCT ( 'Table'[OppID] ),
"Stage 1", CALCULATE ( SELECTEDVALUE ( 'Table'[OldValue] ), 'Table'[Column] = 1 ),
"Stage 2", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 1 ),
"Stage 3", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 2 ),
"Stage 4", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 3 ),
"Stage 5", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 4 )
)
Here is my test result.
@v-eachen-msft I was super excited for this, but it threw an error for me when attmpting to execute the table:
"A table of multiple values was supplied where a single value was expected."
Any thoughts?
Hi @jdonovan ,
Do you have multiple OppIDs?
@jdonovan you should add another column for stage and then it should be easy
Stage =
SWITCH ( Table[Old Value],
"Prospecting", "Stage 1",
"Qualifying", "State 2",
"Estimating", "Stage 3",
"Proposing", "Stage 4",
"Stage 5"
)
and use this new column on columns in the matrix visual
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos 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.
Not sure that would work due to our lack of restriction. Unfortunately, our Salesforce team hasn't locked down starting positions, at least not until recently. So a salesperson could enter an opportunity in the estimating stage and that would technically be Stage 1. And then from there it moves to Proposing (or wherever) which would be Stage 2 for that opportunity.
Weird, I know.
@jdonovan I guess then you need to rank the status and then based on the rank, set the stage
read more about ranking here.
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
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 I took a brief shot at this using Rank.EQ, but not RankX. Will try with RankX and see if it works out.
When I attempted to to flatten out the stages using if statements referring to the Rank.EQ results, it created a separate row at Stage3 (i think due to the criteria)... will see how this goes.
Either way, I appreciate all the suggestions!
Thanks,
John
@parry2k so i got the rank down, but now producing the columns so they show in one singular row appears to be an issue. I'm using an IF statement to grab each stage and the rank is based on the date the stage changed:
Stage1 = if( [StageRank] = 1, [OldValue])
Stage2 = if( [StageRank] = 1, [NewValue])
Stage3 = if( [StageRank] = 2, [OldValue])
It results in the following
OppID | Rank | Stage1 | Stage2 | Stage3 |
ABC123 | 1 | Prospecting | Qualifying | |
ABC123 | 2 | Estimating |
Even when i remove the rank field from the table visual it still displays like the above. Any ideas as to how to get everything on the same row? Should I use something besides an IF statement?
Thanks!
@jdonovan hmmm, I think you should be a calculation to tag Stage 1 or 2 or 3..., it is like category with stage 1, 2, 3 values in it based on your rank
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.
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 |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |