Waterfall Categories

[Anonymous]

I have some data in a simple dataset and I am trying to create a measure or calulated column that will created the appropriate catagories to use for a simple waterfall chart.  The data is below.  In excel, I can create the catagories by summing the colums.  I am trying to get the following catagories:

• Start (starting pipeline)
• New (new deals in the pipeline)
• Weighted value change (deals in the pipeline that changed sales stages so their weighed value changed)
• Contract value change 
• Deals Won
• Deals Lost
• Ending Pipeline

The problem that I am having is that some deals may fall in multiple catagories (a deal had a contract value increase and was won).   In which case I want to capture the full "Won" amount in Won and the amount that changed in weighed value change.  The if/then that I tried to create didn't work because I can't capture multiple catagores.

Deal - ID	Deal - Title	Start Pipeline	New Deals	Won Deals	Weighted Value Change	Lost	Contract Value Change	End
1	Deal 1	1,000	-	(1,200)	200	-	-	-
2	Deal 2		1,000	-	-	-	-	1,000
3	Deal 3		1,000	-	-	-	-	1,000
4	Deal 4	1,000	-	-	-	-	-	1,000
5	Deal 5	1,000	-	(1,000)	-	-	-	-
6	Deal 6	1,000	-		2,000	-	-	3,000
7	Deal 7	1,000	-	-	-	(1,000)	-	-
8	Deal 8	1,000	-	-	-	(1,000)	-	-
9	Deal 9	1,000	-	-	-	(1,000)	-	-
10	Deal 10	1,000	-		(100)	(900)	-	-
11	Deal 11	1,000	-	-	-	-	1,000	2,000
12	Deal 12	1,000	-	-	-	-	1,000	2,000
13	Deal 13	1,000	-	-	-	-	-	1,000
14	Deal 14	1,000	-	-	-	-	-	1,000
15	Deal 15	1,000	-	-	-	-	-	1,000
16	Deal 16		1,000	-	-	-	-	1,000
17	Deal 17	1,000		-	-	(1,000)	-	-
18	Deal 18	1,000		-	(500)	(500)	-	-
19	Deal 19		1,000	-	-	-	-	1,000
20	Deal 20		1,000	-	-	-	-	1,000

[MFelix]

Hi @Anonymous ,

Is the information you present in the format of your data or is this treated and has you would place it in excel?

If the information is in the final format you should unpivot the values an use it on your chart check example below:

Is this the final result? If yes please tell me and I will guide you step by step.

[Anonymous]

Miguel,

Thanks for looking at this.  I am not sure that I understand your question on the data but what I pasted above is a demo subset of the data.  I have 50 or so other comumns in the dataset that don't pertain to the waterfall in any way which makes upivoting the data tricky.  Is there a way to upivot the columns above wich pertain to the waterfall without impacting the rest of the datset?

Thanks

[MFelix]

Hi @Anonymous ,

You can unpivot only some columns the questions is that the other columns will have repeated values.

In this case believe that the best option is to create a disconnected table and make a switch measure or create a measure for each of your options.

Do you want me to prepare a sample file with one of the options above?