cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
oldhasbeen Frequent Visitor
Frequent Visitor

Creating funnel charts from awkward data

Hi

 

I have a problem from the world of British Further Eduction(FE):

 

I've demonstrated how Funnel Charts could be useful in FE, from this sample data I created:

FE1.PNG

 

Giving a funnel chart like this:

 

FE2.PNG

 

Which is great BUT, unfortunately, in the real world the source data isn't in such a convenient format. Here are 2 sample files of more realistic data formats:

 

(a) in this format, the "Status" column shows which stage the applicant has achieved; (full file can be found at 

https://1drv.ms/x/s!AmxJyApgEAcYgo8NoDs8mx7lBf-Hxg)

FE3.PNG

 

(b) In this file, a "Y" indicates the status stated in the column header has been achieved (file is at https://1drv.ms/x/s!AmxJyApgEAcYgo8OZ7cZXMHgwEI70A)

 

FE4.PNG

 

Statuses are defined on the following file (at https://1drv.ms/x/s!AmxJyApgEAcYgo8PLoR2pPl2qJbUtg)

FE5.PNG

 

I've tried using Power BI, with the Funnel Chart desktop visualisation, to get a meaningful funnel chart (i.e. like the first one in this post that I generated from my own test data) from either real-world input file formats, without success - could anyone advise me how I might achieve this without resorting to VBA to reformat the input data?

 

Thanks for your help!

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Creating funnel charts from awkward data

Hi @oldhasbeen

 

You may try to use 'Unpivot Only Selected Columns' in Query Editor as below. Then create relationships for the 3 sheets. Last, you may create measure or drag the columns to Funnel Visual as requested.

1.png

1.png

 

Regards,

Cherie

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Creating funnel charts from awkward data

Hi @oldhasbeen

 

You may try to use 'Unpivot Only Selected Columns' in Query Editor as below. Then create relationships for the 3 sheets. Last, you may create measure or drag the columns to Funnel Visual as requested.

1.png

1.png

 

Regards,

Cherie

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
oldhasbeen Frequent Visitor
Frequent Visitor

Re: Creating funnel charts from awkward data

Thank you Cherie - easy when you know how, I've used my data to produce the following Funel Diagram:

 

FE6.PNG