Creating dynamic Load Duration Curves, dynamic sorting of linechart from largest to smallest y-value

[Anonymous]

Hi all Power BI wizards, 

I have a large dataset with various energy values for each hour across several years. Each unique hour has 3 sets of values (one of each of the 3 different pricing zones). I use a slicer on the timestam data (date+time) in column 'HourDK', and a slicer to choose between one of the 3 pricing zones in column 'PriceArea'. 

I can show the data on a line-chart for the selected timespan with hours on the x-axis (set to Continuous type - I do not want Catagorical type). -See the upper linechart in the Report view screenshot, note the equally distributed datapoints.

I need to Load Duration Curves (where the same data is shown sorted from the largest value first (at 0 on the x-axis) and the lowest value at the end (at the end of the x-axis) -here the x-axis would typically be shown as percentage (%). 

However, the chart won't let me "Sort by" in the "..." menu at the upperright corner of the chart. 

I have tried to make both new columns where the timestamp 'HourDK' is re-aranged according to the correct sorting (largest to smallest) of y-axis values (in the example I use the column 'Percent_REofConsumption') and given in a percent value (so the x-axis goes from 0-100). However, I cannot make it dynamic, i.e. the percentage value is calculated according to the total number of hours in dataset (the full 'HourDK') and not only the selected timespan in the slicer. This results in wrong Load Duration Curves (LDC) where the distance between each datapoint in not equally distributed. -See the lower linechart in the Report view screenshot, note the non-equally distributed datapoints.

So, how do a make these LDC charts with the following requirements:
* Slicer for both 'PriceArea' and 'HourDK' must work on both top and bottom chart
* The LDC chart is showing the y-vaues sorted from largest to smallest, where the largest i at x=0% and the smallest value is at x=100% no matter the selected timespan in the slicer (and updates automatically)
* The charts show all the selected data (using Continuous type in x-axis) no matter the selected timespan, so no horizontal scroll is added at the lower part of the chart (as if I use Catagorical type in x-axis).

Attached is:
*Screenshot of 3 selected days where the wrong distribution of data is clearly visiable (see above)
*Screenshot of 1 full year where the wrong distribution becomes almost impossible to see due to the large share of the total datapoints chosen (see below)

Link to testing copy of the Power BI Dashboard:   240524_PowerBI visuals_test.pbix
Link to dataset without modifications:  MasterData_Processed.xlsx
Columns made in Power BI:
* Rank = RANKX(ALL(Sheet1), Sheet1[Percent_REofConsumption], , DESC, DENSE)
* Percentage = DIVIDE(Sheet1[Rank], COUNTROWS(ALL(Sheet1)))

My problem is quite similar to the one found on StackOverFlow here: https://stackoverflow.com/questions/70997174/responsive-duration-curve-in-power-bi but this solutions does not work either.
I have also, without success, tried these:
https://community.fabric.microsoft.com/t5/Desktop/Sort-line-graphs-from-highest-value-to-lowest-Load...

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Duration-curve/m-p/1326557

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-deskto...

Please help, thanks.

[Anonymous]

Hi again @Anonymous , 

Could I kindly ask you to reply on this. I am scratching my head to find a solution 🙂 

Best regars

[Anonymous]

Hi @Anonymous ,

What is your specific result? Please explain it in detail.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous 

Thanks for your reply, much appriciated. 

My specific result: I need make LDC curves of various values (first of for 'Percent_REofConsumption') across variable timespans (chosen by the slicer linked to 'HourDK') with a value for each hour.

The LDC chart (could be line or bar chart) must be sorted acording to the values with the largest first and the lowest at the end. In the LDC chart the x-axis i represented as 0-100% and the values are evenly distribted across the axis no matter the chosen timepan (selected in the slicer).

Simple example below:

Full dataset, raw:

Hour	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
Value	4	7	2	3	9	10	22	24	26	18	17	13	11	12	14	15	8	6	5	1

Normal line chart on full data:

Full dataset (hour 1-20), for LDC curve (hours in percent and values sorted):

%	5	10	15	20	25	30	35	40	45	50	55	60	65	70	75	80	85	90	95	100
Value	26	24	22	18	17	15	14	13	12	11	10	9	8	7	6	5	4	3	2	1

LDC chart for full data:

Selected dataset (only hour 1-10), for LDC curve (hours in percent and values sorted):

%	10	20	30	40	50	60	70	80	90	100
Value	26	24	22	18	10	9	7	4	3	2

LDC chart for selected data (hour 1-10):

I hope this clarifies. Please let me know if more information in needed, and for easy test kindly try with the data provided in my first post. 

Best regards,

Niels

[Anonymous]

Hi  @Anonymous ,

Below is my table:

I create two column:

* Rank = RANKX(ALL('Table'), 'Table'[ue], , DESC, DENSE)

* Percentage = DIVIDE('Table'[* Rank], COUNTROWS(ALL('Table')))

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous , 

Thank you very much for your time and support. 

Unfortunately, when I try to replicate your approach I am still left with the problem that the LDC datapoints are not equally distributed across the x-axis. 

I have made an identical copy of your approach, but I cannot get the same equal distance on the x-axis. -I also cannot change the x-axis to % in the "Visual" menu. 

Please see attached screenshot of the problems here: 

What do you suggest? 

Best regards,

Niels

[Anonymous]

Hi @Anonymous ,

Try to change the x-axis type to Categorical to get the same equal distance.I placed the percentage calculation column directly on the x-axis.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous , 

Thanks for your swift reply.

Setting the x-axis type to Catagorical is not an option, it needs to be Continuous. Otherwise a horizontal scrolling-bar will apear for larger data sets. My actual data set must be able to show several thousands datapoints at once in the chart without the need to scroll back and forth.. 

Do you know how to fix it? 

I got the percentage working by setting the data type for the column to "percentage" in the Table View. 

Thanks, 

Br Niels