Forum Discussion

zeitgeist's avatar
zeitgeist
Regular Visitor
7 years ago

Funnel Chart filtered by month

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 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI zeitgeist,

     

    I'd like to suggest you do 'unpivot columns' operations on your table, then you can summary these records as your wish.

     

    Full query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VVFtDkMhCLvL+71k8qVwlmX3v8akuI2XGDRphba8Xteg5z48yK/HZTx3DcrnuN6PDXODnWVXmrrr0sKl4SFjV673+a8NZx7Znzzb+CFYI9CcKy+xVLCKMDvBHB0oaRZFWI2w4IAg9CvRuwWDBYWF8z8aPidBYQqgYSDQ6AMiRzMa0LYDQs/QVxGkkgTeQ4yBCZFRBRcuN4uhPwleFqinKMOzA6NPHIm3FEPmX6N4MW4ximNTCww7Q3qOTLjUMEprV9STJIVCNQVjL+v9AQ==", 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}},"en-GB"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Type"}})
in
    #"Renamed Columns"


    Regards,

    Xiaoxin Sheng