Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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
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.
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
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.
Regards,
Frank
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!