Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
zeitgeist
Regular Visitor

Funnel Chart with filtered months

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

 

DateAccessedPurchasedPaid
01/01/20185269150
02/01/201882316474
03/01/201893021890
04/01/2018220618380
05/01/2018166713557
06/01/2018158611759
07/01/201872612374
08/01/201885314479
09/01/2018661185105
10/01/2018797244106
11/01/201887724391
12/01/201890119092
13/01/2018159418584
14/01/2018308120195
15/01/20181936244138
16/01/20181380274154
17/01/20182118451240
18/01/20181485454247

 

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:

 

 JanuaryFebruaryMarch
1-Accessed373272858519307
2-Purchased571452184876
3-Paid277924782381

 

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

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-frfei-msft
Community Support
Community Support

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")

1.PNG

 

2. Group the table by the new custom column.

 

2.PNG

 

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

 

3.PNG

 

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

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

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.

 

1.PNG2.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.