Funnel chart

Question & Answer
, , , , , ,
1

Hi All,

I have a funnel chart where I put the measure of unique customers per stage of the journey. For example, the funnel shows 100% at the first level, 85% at the second level, and so on. The funnel chart does this automatically. I need to show the same values, but in the form of a table, but I’m not getting to do it. How could my calculated field be so that it shows me exactly as the funnel chart shows?

Thank you!

2

Hi @July, try using the percentOfTotal function

In case you need further assistance, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here. Details on using Arena can be found here - QuickSight Arena

1 Like
3

Hello @Xclipse

I can’t use PercentualOfTotal because it doesn’t work.
I must show the first step 100% as per the funnel image, not the total
The measure used for the funnel image was quantity of customers (count distinct)
How to do that?

Tks!

image
image811×516 34.9 KB

4

From funnel
image

5

Hi @July, thanks for the detailed explanation.

The formula used to calculate the percentages seems to divide each number by the first number ( 103.869 ) (which corresponds to 100%) and then multiply by 100. The results are as follows:

  • For ( 103.869 ), the calculation is ( (103.869 / 103.869) times 100 = 100% ), which matches the provided percentage.
  • For ( 51.08 ), the calculation is ( (51.08 / 103.869) times 100 approx. 49.18% ), which matches the provided percentage.
  • For ( 4.993 ), the calculation is ( (4.993 / 103.869) times 100 approx. 4.81% ), which does not match the provided percentage of ( 9.77% ).
  • For ( 4.812 ), the calculation is ( (4.812 / 103.869) times 100 approx. 4.63% ), which does not match the provided percentage of ( 96.39% ).

Please can you provide the formula for the “off visual-field (customers (count distinct)” on how 4.993 and 4.812 percentage calculation is derived? Then we can find a pre-built function or create a custom formula that you maybe able to use from here.

Also, please try creating a sample dashboard with sample dataset using QuickSight Arena and share the link here.

Alex

6

Hi Alex,

Thanks for your response

I need a calculated field because I have to put it in the table column. I don’t think there could be several calculation fields.

Could you please help me create a custom calculated field for this?

July

7

Hi @July – based on what I can see, you may need a combination of calculation and aggregation to accomplish this in a table.

I would start by looking at this blog.

Then also, review this community forum.

If you still need further assistance, consider creating a sample dashboard with a sample dataset in Arena that illustrates your problem. Post it in a new thread for better visibility. You can find detailed instructions on how to useQuickSight Arena.

Thanks,

Alex

8

Sorry, but I thought you could help me with some tips on how to set up calculated fields like your colleagues do, not send me a blog with a lot of functions.

This left me more confused.

9

Hi @July – sorry, I don’t have access to your data. We need to figure out the formula used to calculate the percentages. Seems, we are dividing each number by the first number ( 103.869 ) (which corresponds to 100%) and then multiply by 100. But then which is the first number, maybe we can use Sort or other Max functions. In any case, the results are as follows:

  • For ( 103.869 ), the calculation is ( (103.869 / 103.869) times 100 = 100% ), which matches the provided percentage.
  • For ( 51.08 ), the calculation is ( (51.08 / 103.869) times 100 approx. 49.18% ), which matches the provided percentage.
    How?
  • For ( 4.993 ), the calculation is ( (4.993 / 103.869) times 100 approx. 4.81% ), which does not match the provided percentage of ( 9.77% ).
  • For ( 4.812 ), the calculation is ( (4.812 / 103.869) times 100 approx. 4.63% ), which does not match the provided percentage of ( 96.39% ).

Let’s keep this discussion open for the next three days to see if anyone else in the community has suggestions. It might be challenging to address this question on the forum without specific details about the formulas or data used in the last two calculations. If we didn’t use the funnel chart, what other methods could we have employed to derive these percentages?

11

Thank you for letting us know. I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf. Hope this helps!