Create Line Graph from Calculated Values

[Turbat1]

Hello all,

I have got on issue , I have got a Dataset where I want to create Line chart based on calculated values.

Y Axis shall be the summarized mileage of a car fleet and on
X axis I want to see the calculated percentiles (10,20…90) of the Fleet,
( I calculated the percentiles of 10,20…90)
In Power BI it looks like this:

I can calculate in Quiskight the percentiles with “percentile function” and show is as “table”, here an example for 10 and 20 percentiles:

But how can I create a line graph out of it ?

Thanks in advance to all…

Regards

Turhan

[ErikG]

HI @Turbat1
whats the issue if you bring it to a line chart?
BR

[Turbat1]

Hello @ErikG ,

it is expecting more Dimensions at X axis, but I can put anything at X or y axis, it shows noting:

or movin the calculated Values to X axis is not possible:

[ErikG]

You need a dimensional field (like “10”, “20”…) for the x-axis

What about a Scatter Plot in case you want to compare two different measures?

[Turbat1]

Hello,
it is quite the same:

Regarding the Dimension filed, isn’t it possible to create Dimesnion field in a table ? or use any other possibility `?

Regrads
Turhan

[Turbat1]

Hello Erik,

did you have any other sugesstions ?
Is it possible to create 2. Table where I can Calculate the percentiles based on the rows ?

Please advice…and thanks in advance…

regrads

Turhan

[ErikG]

HI @Turbat1

you dont need a second table. You just need a dimemsional field. Do you have any fields to use?
Maybe you can share your dataset fields?

BR

[Turbat1]

Hello Erik, thanks for your support:

Given is this Dataset, vehicles per year and driven km per year:

Car	year	Total driven per year
Auto_1	2019	58980
Auto_4	2019	143029
Auto_4	2021	159145
Auto_5	2019	118585
Auto_4	2018	79643
Auto_1	2018	19477
Auto_5	2018	235
Auto_3	2018	31627
Auto_3	2020	87701
Auto_3	2021	105312
Auto_1	2020	40630
Auto_4	2020	137708
Auto_3	2019	112029
Auto_2	2021	85562
Auto_5	2021	100556
Auto_1	2022	56754
Auto_2	2022	90793
Auto_5	2020	80682
Auto_5	2022	66631
Auto_4	2022	163086
Auto_2	2019	119691
Auto_1	2021	44589
Auto_2	2020	118083
Auto_3	2022	126114
Auto_2	2018	22681

The Percentiles shall be calculatet, (percentile calculation is possible in Quicksight, here the calculation in Excel), for 10,20,30,…percentiles:
10 26259,4
20 43797,2
30 60510,2
40 80266,4
50 87701
60 102458,4
70 116872,2
80 120975,6
90 140900,6

But a Diagram like shown is not possible: (here in Excel created):

I am sorry but I can’t upload the excel, but it loosk like this:

And the Calculation for Excel , following formula is used: =QUANTIL.INKL(Values; XY)

Thanks in advance…

Regards
Turhan

[David_Wong]

Hi @Turbat1,

I think you need to use a custom SQL query to create this table as your dataset.

[Turbat1]

Hello David,

thanks for your advice, yes this is possible with Quicksight:

But how is then with “Filter” Optionality when I want to calculate it “on the fly” during Filter selection , right now it is static based on the SQL statement.

In Power BI you can create an additioan table (or import) additiona table) with the values (10,20,30…) and then you can make calculations based Filter settings.

Regards

Turhan

[ErikG]

Hi @Turbat1
as @David_Wong mentioned I would suggest to create a row based table. Means you should transpose your query.

Filtering depends on your calculations. You could e.g. use database parameters.

BR

[Turbat1]

Hello @ErikG , hello @David_Wong ,

thank you for your fedback, I created somthing in Athena (which I think also work in Quicksigh),:

What I didn’t understand, how you can now get it in that stage that tgis is filterable ?
Even when I am cerating this table, I have to select before to shich Column I have to group etc.
How can I use Database parameters then in Quicksight ?

Thnak in advance…

regards

Turhan

[ArunSanthosh]

Hi Turhan @Turbat1 ,

Dataset parameters option can help you in passing filter values to your custom sql.
Read more here - Documentation link

Regards,
Arun Santhosh
Pr QuickSight SA