Sorting a linechart based on y-axis values

[Anonymous]

Hi all,

I have a large dataset with the following two columns:

1) 'HourDK' as datetime data, Data type = Date/time, format = "dd-mm-yyyy HH:MM:SS" 

2) 'Values' as the values, Data type = Decimal number, format = General

I have a slicer connected to 'HourDK' where I can choose various timespands. 

I need a linechart where the data is sorted decending acording to 'Values'. However, the x-axis format NEEDS to be Continuous and not Catagorical (to avoid the horizontal scroll-bar). - see the lower line chart in attached screenshot

How do I solve this? 

Furthermore; would be great if the x-axis could be represented in percentage from 0-100% dynamically, so that no matter which timespan is selected in the slicer the highest 'Value' is at x=0% and the lowest 'Value' is at x=100%

Please help. 

Best regards

Niels

[lbendlin]

Create a Pareto running total column and use that for the X axis.

[Anonymous]

Hi @lbendlin , 

Thanks for your reply. 

I have tried to search for various solutions on this without luck. 

Can I kindly ask you to give an example / step-by-step guide? 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous]

Hi @lbendlin , 

Please find the .pbix file here (I am not an MS employee or superuser and cannot attached directly), it only contains datetime and dummy values for one (1) month (31 days x 24 hours). Link:  240621_Testing data_one month.pbix 

The two original data columns are 'HourDK' and 'Values'.

Additionally I have created the following three columns to:

* 'FilteredRank' - to create a calculated dynamic rank

* 'FilteredCumulativeTotal' - to create a calculated dynamic cumulative total

* 'DynamicCumulativePercentage' - to calculate the dynamic cumulative percentage for the x-axis. 

You find the following in the dashboard: 

* In top: Slicer applied to 'HourDK' to select a certain datetime span.

* In middle: A normal linechart that shows the 'HourDK' on x-axis and 'Values' on y-axis in the timespan selected in the slicer.

* Below: A linechart that correctly shows the slicer-selected timespan recalculated as percentage on the x-axis and the corresponding 'Values'. This chart is sorted so it shows the slicer-selected data decending. 

The problem is that I cannot find a way to make the percentage calculation dynamically and evenly spread across the x-axis, i.e. so that no matter what selection I choose in the slicer, the largest 'Values' is at x=0% and the lowest 'Values' is at x=100% and equally distributed at the same time. --> so if 1 day (24 hours) is choosen those 24 datapoints will be shown from 0-100% evenly distributed, and if 10 days (240 hours) is choosen those 240 datapoints will shown from 0-100% evenly distributed. 

Using Catagorical type on x-axis is NOT an option since I need to be able to see all selected data in the chart without a horizontal scrollbar. 

As you can see the Markers dots are currently not equally distributed which is the core problem. -Hence I cannot give you an example of the expected outcome rather that described above. 

I hope this clarifies the problem. 

Look forward to your solution. 

Best regards,

[lbendlin]

Why would these data points be evenly distributed when you want to show the y axis by value?

[Anonymous]

Hi @lbendlin ,

Thanks for your reply, I appriciate your attempt. However, this has not solved the issue. 

The solution you have provided results in a chart with decreasing distance between the dots the further towards x=100% on the x-axis. 

I need the datapoints (no matter the selected datetime-span in the slicer) to be equally distributed from x=0% to x=100% so it is easy to see how many 'Values' are above / below a certain level. 

E.g.: When setting the slicer on [all data], 'Values' are above 100 in ~20 percent of the time, or when setting the slicer on [06-06-2021 to 09-01-2021], 'Values' are below 100 in ~100 percent of the time and below 50 in ~60% of the time.. 

I need to be able to make such "Duration Curve" analysis. 

Look forward to your reply.

[lbendlin]

Change the granularity of the buckets. Right now it is set at 0.1%, but it sounds like you want it set at 10%.

[Anonymous]

Hi @lbendlin ,

Nope, that does not work. 

Kindly read what I am asking for. 

There needs to be a equal amount of datapoints in the two charts. But the Duration Curve chart must be sorted decending according to 'Values' and plottet equally distributed from x=0% to x=100%. 

This is a screenshot of the granuality set to 1%, it set to 10% it gets even worse..