How to create a bar chart with MTD, QTD, YTD on Y axis

[hymieho]

Help, I can't figure out how to do this. This is what I am trying to achieve:

Here is my dataset. I have a measure for Losses, a Yes/No indicator for YTD_ind & QTD_ind, and a WeeksAgo column (1=last week/LW).

I cannot wrap my head around how to build a table I can use for the bar chart that I want. I have a feeling that I need to do this in Power Query with Pivot/Unpivot somehow?

[hymieho]

This turned out to be a series of steps. I had to go in order of small time to big time (from Last Week -> R30D -> QTD -> YTD) and basically repeat the same series of steps to get what I needed, and with this table I was able to build a beautiful Tornado chart. It probably would have been a lot easier to just do the aggregation in SQL as a separate table but this was a fun learning experience.

~hymie

let
Source = Pipeline,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Value (USD)", "Is YTD", "Is QTD", "Is R30D", "Is LW", "Opp State"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Is LW]), "Is LW", "Value (USD)", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Y", "LW"}, {"N", "LW-N"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,0,Replacer.ReplaceValue,{"LW"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"LW-N"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "LW Total", each [#"LW-N"]+[LW]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"LW-N"}),
#"Pivoted Column1" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Is R30D]), "Is R30D", "LW Total", List.Sum),
#"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column1",{{"Y", "R30D"}, {"N", "R30D-N"}}),
#"Replaced Value7" = Table.ReplaceValue(#"Renamed Columns1",null,0,Replacer.ReplaceValue,{"R30D"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7",null,0,Replacer.ReplaceValue,{"R30D-N"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value8", "R30D-Total", each [#"R30D-N"]+[R30D]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"R30D-N"}),
#"Pivoted Column2" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Is QTD]), "Is QTD", "R30D-Total", List.Sum),
#"Renamed Columns2" = Table.RenameColumns(#"Pivoted Column2",{{"Y", "QTD"}, {"N", "QTD-N"}}),
#"Replaced Value4" = Table.ReplaceValue(#"Renamed Columns2",null,0,Replacer.ReplaceValue,{"QTD"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4",null,0,Replacer.ReplaceValue,{"QTD-N"}),
#"Added Custom2" = Table.AddColumn(#"Replaced Value5", "QTD-Total", each [#"QTD-N"]+[QTD]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"QTD-N"}),
#"Pivoted Column3" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Is YTD]), "Is YTD", "QTD-Total", List.Sum),
#"Removed Columns3" = Table.RemoveColumns(#"Pivoted Column3",{"N"}),
#"Replaced Value6" = Table.ReplaceValue(#"Removed Columns3",null,0,Replacer.ReplaceValue,{"Y"}),
#"Renamed Columns3" = Table.RenameColumns(#"Replaced Value6",{{"Y", "YTD"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns3", {"Opp State"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Opp State", "Attribute"}, {{"Sum", each List.Sum([Value]), type number}}),
#"Renamed Columns4" = Table.RenameColumns(#"Grouped Rows",{{"Sum", "Total"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns4", "Custom", each if [Attribute] = "LW" then "1" else if [Attribute] = "R30D" then "2" else if [Attribute] = "QTD" then "3" else if [Attribute] = "YTD" then "4" else null ),
#"Renamed Columns5" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Attribute SORT"}})
in
#"Renamed Columns5"

[hymieho]

This worked, however because it is a different table that has no relationship to the original data set, clicking on a section of the tornado will not filter a details table/grid under it. So I feel I am back at square one... any ideas how I can create a tornado using the orginal flags Is LW (last week), Is R30D (past 30 days), Is QTD, is YTD) so cross filtering between visualizations works?

[hymieho]

Just to keep you all up to date on my journey: today I created a solution that works, but with its own drawbacks. For each of LW, R30D, QTD, YTD, I built a table with a new column called "Historical View" that has the filter value (LW, R30D, etc.). These tables contain just the subset of the original table filtered to value (LW, R30D, etc.). I then merged these tables, so now each record is duplicated for each historical view it appears in. For example all records from LW will be in R30D, all from QTD will be in YTD, etc. This allows me to build the tornado chart that I want because now all values (LW, R30D, QTD, YTD) are in a single dimension "Historical View". The drawback is, of course, that at the bottom of the report I have a table to show record details, and many records will be duplicated. Since this page is just for analysis this is acceptable, but not ideal.

Here are the details how I did it:

1. In Power Query I created series of 4 tables derived from the main table. Here's the one for last week (LW):

let
    Source = #"Closed Won/Lost",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"OpportunityGUID", "Opp State", "Opp State SORT", "Customer Name", "Created Date", "Opp Resolved Date", "Sales Cycle (days)", "Sales Person", "Sales Stage", "Sales Stage SORT", "Pipeline (lb)", "Price to close", "Currency", "Estimated Close Date", "Close Probability", "Resolved By", "Resolution Description", "Version Number", "Supplier", "Product Line", "PL2", "SKU", "SKU Description", "ProductID", "Grade", "Years Ago", "Days ago", "Months Ago", "Weeks Ago", "Is LW", "Territory", "Value (USD)"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Is LW", "Historical View"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Y","LW",Replacer.ReplaceText,{"Historical View"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Historical View] = "LW"))
in
    #"Filtered Rows"

This table is a subset of just records related to Last Week with a new column:

Once I have all 4 tables, I merge them into my final table:

let
    Source = #"Closed Won/Lost MERGE - LW",
    #"Appended Query" = Table.Combine({Source, #"Closed Won/Lost MERGE - R30D", #"Closed Won/Lost MERGE - QTD", #"Closed Won/Lost MERGE - YTD"}),
    #"Added Conditional Column" = Table.AddColumn(#"Appended Query", "Custom", each if [Historical View] = "LW" then "1" else if [Historical View] = "R30D" then "2" else if [Historical View] = "QTD" then "3" else if [Historical View] = "YTD" then "4" else "5" ),
    #"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Historical View SORT"}})
in
    #"Renamed Columns"

Et voila. Now if only I could figure out how to have the detail table only show YTD, but only when there are no other filters otherwise filter accordingly.

[hymieho · Accepted solution]

Nailed it. To eliminate duplicate values from the detail table visualization, I added a column to join the original table (as a Dimension) to the new Merged tabled, then used the dimension table (with unique values) in the table visualization, turned on cross-filtering, and that solved the problem. You will have to go back read this whole thread to see the steps that got me to this point. Thanks for following along and hope this helps somebody. 

~hymie

[hymieho]

For those of you still playing along at home, I just figured out how to do this MUCH more simply in DAX. YOU'RE WELCOME!

Final result:

Small aside: the Tornado custom visual from Microsoft is a little jacked: the Legend on/off no longer works (when I turn it on, it bumps the visual down to clear the area where the legend would be, but it is invisible). Plus I had to add a Sort By column because it was sorted in a seemingly random way and there is no option in the control to specify the sort order like there is for other controls. 

~hymie

Tornado = UNION(
--LW
SUMMARIZECOLUMNS(Opportunities[Opp State],
Opportunities[Opp State SORT],
Opportunities[OpportunityGUID],
Opportunities[Sales Professional],
Opportunities[Sales Manager],
Opportunities[Territory],
FILTER(Opportunities, Opportunities[Is LW]="Y" && (Opportunities[Opp State]="Won" || Opportunities[Opp State]="Lost")),
"Pipeline (USD)", SUM(Opportunities[Pipeline (USD)]),
"Sales (LCUR)", SUM(Opportunities[Sales (LCUR)]),
"Time Frame", "LW",
"Time Frame Sort", 1)
,
--R30D
SUMMARIZECOLUMNS(Opportunities[Opp State],
Opportunities[Opp State SORT],
Opportunities[OpportunityGUID],
Opportunities[Sales Professional],
Opportunities[Sales Manager],
Opportunities[Territory],
FILTER(Opportunities, Opportunities[Is R30D]="Y" && (Opportunities[Opp State]="Won" || Opportunities[Opp State]="Lost")),
"Pipeline (USD)", SUM(Opportunities[Pipeline (USD)]),
"Sales (LCUR)", SUM(Opportunities[Sales (LCUR)]),
"Time Frame", "R30D",
"Time Frame Sort", 2)
,
--QTD
SUMMARIZECOLUMNS(Opportunities[Opp State],
Opportunities[Opp State SORT],
Opportunities[OpportunityGUID],
Opportunities[Sales Professional],
Opportunities[Sales Manager],
Opportunities[Territory],
FILTER(Opportunities, Opportunities[Is QTD]="Y" && (Opportunities[Opp State]="Won" || Opportunities[Opp State]="Lost")),
"Pipeline (USD)", SUM(Opportunities[Pipeline (USD)]),
"Sales (LCUR)", SUM(Opportunities[Sales (LCUR)]),
"Time Frame", "QTD",
"Time Frame Sort", 3)
,
--YTD
SUMMARIZECOLUMNS(Opportunities[Opp State],
Opportunities[Opp State SORT],
Opportunities[OpportunityGUID],
Opportunities[Sales Professional],
Opportunities[Sales Manager],
Opportunities[Territory],
FILTER(Opportunities, Opportunities[Is YTD]="Y" && (Opportunities[Opp State]="Won" || Opportunities[Opp State]="Lost")),
"Pipeline (USD)", SUM(Opportunities[Pipeline (USD)]),
"Sales (LCUR)", SUM(Opportunities[Sales (LCUR)]),
"Time Frame", "YTD",
"Time Frame Sort", 4)
)

[v-yulgu-msft]

Hi @hymieho,

In this scenario, I think you could first Unpivot source table in Query Editor mode, then, in design mode, calculate the Losses measure based on unpivoted table.

Regards,
Yuliana Gu