Funnel Chart with filtered months

[zeitgeist]

Hi, I am a bit new to PowerBI but not to charts, dashboards in general. I have the following table format:

Date	Accessed	Purchased	Paid
01/01/2018	526	91	50
02/01/2018	823	164	74
03/01/2018	930	218	90
04/01/2018	2206	183	80
05/01/2018	1667	135	57
06/01/2018	1586	117	59
07/01/2018	726	123	74
08/01/2018	853	144	79
09/01/2018	661	185	105
10/01/2018	797	244	106
11/01/2018	877	243	91
12/01/2018	901	190	92
13/01/2018	1594	185	84
14/01/2018	3081	201	95
15/01/2018	1936	244	138
16/01/2018	1380	274	154
17/01/2018	2118	451	240
18/01/2018	1485	454	247

And so on for the whole year.

I am building a dashboard with sales data filtered by months, days, etc.

So I wanted to have funnel that has the stages Accessed, Purchased and Paid, showing the sum for each stage according to the a date filter I selected (either for the chart or following the whole dashboard date filter).

I tried many different ways but either I can only create a chart when the data is in this format:

	January	February	March
1-Accessed	37327	28585	19307
2-Purchased	5714	5218	4876
3-Paid	2779	2478	2381

And then I cannot filter it by dates, or I just can't make the funnel work. 

How can I make it work?

Thanks

Decio

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[zeitgeist]

This worked great. Thanks.

I have another problem though, since I have many other data and the actual sales data come from another table, when I try to create relations between the Data table you created and the orders table through the dates the system won't let it because one of the columns should have unique data and they don't (the Data table have 3 lines for each date and the Order table have many lines for each date, each with its own purchase).

When I try to make the relation with the columns from the other tables, it messes with the dates in other charts and the Funnel won't respond to these changes in date.

For example, the Orders table have something like:

OrderDate          ProductBought           Value

01/01/2018           X                                 10,00

01/01/2018           Y                                 20,00

02/01/2018           X                                 10,00

02/01/2018           Z                                 15,00

03/01/2018           X                                 10,00

[Ashish_Mathur]

Hi,

You will have to create a Calendar Table with all unique dates.  Create a elationship from the date column of both Tables to the Date column of the Calendar Table.  In your Calendar Table, write calculated column formulas to extract Year and Month by using =YEAR(Calendar[Date]) and =FORMAT(Calendar[Date],"mmmm").  In which ever visual you now create, drag Year/Month fields from the Calendar Table.

Hope this helps.

[zeitgeist]

Hi, I tried the solution, but i don't think it worked.

I am inserting my dashboard here .

Basically I can't seem to interact the funnel with all the other views and the slicer.

Thanks

Dashboard

[Ashish_Mathur]

Hi,

In this PBI file, when you change the month in the slicer, all charts will respond.  However, when you click on a slice of any Donut chart, the funnel chart will not response because there is no common/connecting column between the Analytics Funnel (2) table and the Plan1 (2) Table.

[v-frfei-msft]

Hi @zeitgeist,

Based on my test, we can take the following steps to meet your requirement.

1, Enter the data as you shared and add a custom column in power query.

Date.ToText([Date],"MMMM")

2. Group the table by the new custom column.

3. DemoteHeaders - Transposed - Promoted headers , then we can get the result as we need. 

Also M code in the Advanced editor for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFdDsQgCITv0ucm8qtwlmbvf40VKm6b7AuafMMw4nUd2LARoB3nodRndYwrHJ8zIBU04lmxy6xDFuWizjAr3ffqlaJEEM5oYWGFtTD2PuJgjclj4b6xWnZjiNQXHoVHpsaMt4PZjq0ZWzJ29XrR3jFzxWAEXRhhe3vMpOzG+YbF98Zs3JzvvQUO0gAbUK4i/T2W4/TgdHNUlx3A5CHgBkEYLBwofVzfgkzt3H8R2f4o2PJvRipU3imyGfMxojlK6n9wLxElA4pKCuYPfb4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Accessed = _t, Purchased = _t, Paid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Accessed", Int64.Type}, {"Purchased", Int64.Type}, {"Paid", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Date.ToText([Date],"MMMM")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Month"}, {{"1-Accessed", each List.Sum([Accessed]), type number}, {"2-Purchased", each List.Sum([Purchased]), type number}, {"3-Paid", each List.Sum([Paid]), type number}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Grouped Rows"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type text}, {"January", Int64.Type}, {"February", Int64.Type}, {"March", Int64.Type}})
in
    #"Changed Type2"

For more details, please check the pbix as attached.

Regards,

Frank

[zeitgeist]

Hi, thanks for the answer.

unfortunately I stoped right at the start. It returned error on the Date.toText function:

A sintaxe de '.' está incorreta. (DAX(Date.ToText([Date],"MMMM"))).

Just to be sure, since I am new to powerBi, I  entered the Data view, selected my source with the table I sent before, right clicked on it and clicked on the "New Column" item.

Is that correct?

Thanks

[v-frfei-msft]

Hi @zeitgeist,

Now in the data view pane. Please check the steps as the picture as below.

Edit Queries - add custom column - enter the formula.

Regards,

Frank

[zeitgeist]

Hi Everyone.

Sorry for the lack of answers but I am away from work these days so I don't have access to the original files. I will get back and try the solution until Friday.

Thanks for the help!