Reputation: 193
Responsive duration curve in Power BI
I would like to create a duration curve (https://en.wikipedia.org/wiki/Load_duration_curve) in Power BI which is responsive to the user's selections (slicers). This would help the user to analyse time series for electricity production; for example, for wind power (slicer "type") in France (slicer "area") during the last year (time slicer). Then, the corresponding slicer settings will filter the original data set to only include the user's choice. This chosen subset of the original data should then be ranked descending according to electricity production. Finally, the ranked data should be plotted with the rank on the x-axis and the respective production values on the y-axis. The whole process could look like this:
Here, an example: First, the hourly electricity production:
Finally, the corresponding duration curve which I want to display in Power BI. It shows the production time series but sorted descending by their production levels instead of being plotted chronologically:
The y-axis shows the observed levels and the x-axis their respective duration, e.g. there has only been one hour with >= 59 MW, but approximately 1500 hours with >= 40 MW.
The question: How to display the load duration curve responsive to the user's selections? As the data includes production levels for different types of production, different regions etc., the user should be able to visualise the corresponding duration curve for whichever type and region etc. she or he is interested in. Therefore, I somehow have to rank the original data after the user's slicer/filter settings have been applied. This is where I struggle. How can that be done in Power BI?
To facilitate, here two days of (fictive) example data with two areas (1 and 2) and two types of production (1 and 2) which you simply can paste into Power BI as new calculated table to have a minimal working example:
data = DATATABLE
(
"id", INTEGER,
"datetimestamp", DATETIME,
"area", INTEGER,
"type", INTEGER,
"point_value", INTEGER,
{
{1001,"21-01-01 00:00",1,1,47},
{1002,"21-01-01 01:00",1,1,51},
{1003,"21-01-01 02:00",1,1,50},
{1004,"21-01-01 03:00",1,1,50},
{1005,"21-01-01 04:00",1,1,51},
{1006,"21-01-01 05:00",1,1,50},
{1007,"21-01-01 06:00",1,1,51},
{1008,"21-01-01 07:00",1,1,51},
{1009,"21-01-01 08:00",1,1,46},
{1010,"21-01-01 09:00",1,1,51},
{1011,"21-01-01 10:00",1,1,46},
{1012,"21-01-01 11:00",1,1,47},
{1013,"21-01-01 12:00",1,1,51},
{1014,"21-01-01 13:00",1,1,48},
{1015,"21-01-01 14:00",1,1,46},
{1016,"21-01-01 15:00",1,1,47},
{1017,"21-01-01 16:00",1,1,48},
{1018,"21-01-01 17:00",1,1,45},
{1019,"21-01-01 18:00",1,1,43},
{1020,"21-01-01 19:00",1,1,45},
{1021,"21-01-01 20:00",1,1,43},
{1022,"21-01-01 21:00",1,1,36},
{1023,"21-01-01 22:00",1,1,36},
{1024,"21-01-01 23:00",1,1,37},
{1025,"21-01-02 00:00",1,1,38},
{1026,"21-01-02 01:00",1,1,36},
{1027,"21-01-02 02:00",1,1,33},
{1028,"21-01-02 03:00",1,1,35},
{1029,"21-01-02 04:00",1,1,42},
{1030,"21-01-02 05:00",1,1,38},
{1031,"21-01-02 06:00",1,1,38},
{1032,"21-01-02 07:00",1,1,37},
{1033,"21-01-02 08:00",1,1,37},
{1034,"21-01-02 09:00",1,1,37},
{1035,"21-01-02 10:00",1,1,35},
{1036,"21-01-02 11:00",1,1,36},
{1037,"21-01-02 12:00",1,1,37},
{1038,"21-01-02 13:00",1,1,37},
{1039,"21-01-02 14:00",1,1,37},
{1040,"21-01-02 15:00",1,1,37},
{1041,"21-01-02 16:00",1,1,40},
{1042,"21-01-02 17:00",1,1,45},
{1043,"21-01-02 18:00",1,1,40},
{1044,"21-01-02 19:00",1,1,37},
{1045,"21-01-02 20:00",1,1,37},
{1046,"21-01-02 21:00",1,1,37},
{1047,"21-01-02 22:00",1,1,37},
{1048,"21-01-02 23:00",1,1,35},
{2001,"21-01-01 00:00",1,2,40},
{2002,"21-01-01 01:00",1,2,41},
{2003,"21-01-01 02:00",1,2,42},
{2004,"21-01-01 03:00",1,2,42},
{2005,"21-01-01 04:00",1,2,42},
{2006,"21-01-01 05:00",1,2,42},
{2007,"21-01-01 06:00",1,2,42},
{2008,"21-01-01 07:00",1,2,43},
{2009,"21-01-01 08:00",1,2,45},
{2010,"21-01-01 09:00",1,2,44},
{2011,"21-01-01 10:00",1,2,43},
{2012,"21-01-01 11:00",1,2,42},
{2013,"21-01-01 12:00",1,2,43},
{2014,"21-01-01 13:00",1,2,44},
{2015,"21-01-01 14:00",1,2,44},
{2016,"21-01-01 15:00",1,2,40},
{2017,"21-01-01 16:00",1,2,40},
{2018,"21-01-01 17:00",1,2,41},
{2019,"21-01-01 18:00",1,2,45},
{2020,"21-01-01 19:00",1,2,44},
{2021,"21-01-01 20:00",1,2,44},
{2022,"21-01-01 21:00",1,2,42},
{2023,"21-01-01 22:00",1,2,42},
{2024,"21-01-01 23:00",1,2,41},
{2025,"21-01-02 00:00",1,2,41},
{2026,"21-01-02 01:00",1,2,42},
{2027,"21-01-02 02:00",1,2,42},
{2028,"21-01-02 03:00",1,2,42},
{2029,"21-01-02 04:00",1,2,41},
{2030,"21-01-02 05:00",1,2,41},
{2031,"21-01-02 06:00",1,2,42},
{2032,"21-01-02 07:00",1,2,42},
{2033,"21-01-02 08:00",1,2,41},
{2034,"21-01-02 09:00",1,2,39},
{2035,"21-01-02 10:00",1,2,41},
{2036,"21-01-02 11:00",1,2,40},
{2037,"21-01-02 12:00",1,2,41},
{2038,"21-01-02 13:00",1,2,42},
{2039,"21-01-02 14:00",1,2,41},
{2040,"21-01-02 15:00",1,2,40},
{2041,"21-01-02 16:00",1,2,39},
{2042,"21-01-02 17:00",1,2,41},
{2043,"21-01-02 18:00",1,2,41},
{2044,"21-01-02 19:00",1,2,42},
{2045,"21-01-02 20:00",1,2,41},
{2046,"21-01-02 21:00",1,2,42},
{2047,"21-01-02 22:00",1,2,41},
{2048,"21-01-02 23:00",1,2,40},
{3001,"21-01-01 00:00",2,1,37},
{3002,"21-01-01 01:00",2,1,38},
{3003,"21-01-01 02:00",2,1,38},
{3004,"21-01-01 03:00",2,1,38},
{3005,"21-01-01 04:00",2,1,39},
{3006,"21-01-01 05:00",2,1,41},
{3007,"21-01-01 06:00",2,1,41},
{3008,"21-01-01 07:00",2,1,43},
{3009,"21-01-01 08:00",2,1,43},
{3010,"21-01-01 09:00",2,1,43},
{3011,"21-01-01 10:00",2,1,45},
{3012,"21-01-01 11:00",2,1,46},
{3013,"21-01-01 12:00",2,1,48},
{3014,"21-01-01 13:00",2,1,48},
{3015,"21-01-01 14:00",2,1,47},
{3016,"21-01-01 15:00",2,1,47},
{3017,"21-01-01 16:00",2,1,47},
{3018,"21-01-01 17:00",2,1,46},
{3019,"21-01-01 18:00",2,1,46},
{3020,"21-01-01 19:00",2,1,46},
{3021,"21-01-01 20:00",2,1,46},
{3022,"21-01-01 21:00",2,1,41},
{3023,"21-01-01 22:00",2,1,41},
{3024,"21-01-01 23:00",2,1,41},
{3025,"21-01-02 00:00",2,1,41},
{3026,"21-01-02 01:00",2,1,40},
{3027,"21-01-02 02:00",2,1,40},
{3028,"21-01-02 03:00",2,1,39},
{3029,"21-01-02 04:00",2,1,39},
{3030,"21-01-02 05:00",2,1,41},
{3031,"21-01-02 06:00",2,1,40},
{3032,"21-01-02 07:00",2,1,40},
{3033,"21-01-02 08:00",2,1,40},
{3034,"21-01-02 09:00",2,1,42},
{3035,"21-01-02 10:00",2,1,41},
{3036,"21-01-02 11:00",2,1,41},
{3037,"21-01-02 12:00",2,1,41},
{3038,"21-01-02 13:00",2,1,43},
{3039,"21-01-02 14:00",2,1,44},
{3040,"21-01-02 15:00",2,1,45},
{3041,"21-01-02 16:00",2,1,46},
{3042,"21-01-02 17:00",2,1,47},
{3043,"21-01-02 18:00",2,1,47},
{3044,"21-01-02 19:00",2,1,47},
{3045,"21-01-02 20:00",2,1,46},
{3046,"21-01-02 21:00",2,1,43},
{3047,"21-01-02 22:00",2,1,41},
{3048,"21-01-02 23:00",2,1,40},
{4001,"21-01-01 00:00",2,2,37},
{4002,"21-01-01 01:00",2,2,33},
{4003,"21-01-01 02:00",2,2,35},
{4004,"21-01-01 03:00",2,2,33},
{4005,"21-01-01 04:00",2,2,35},
{4006,"21-01-01 05:00",2,2,25},
{4007,"21-01-01 06:00",2,2,27},
{4008,"21-01-01 07:00",2,2,30},
{4009,"21-01-01 08:00",2,2,35},
{4010,"21-01-01 09:00",2,2,35},
{4011,"21-01-01 10:00",2,2,32},
{4012,"21-01-01 11:00",2,2,37},
{4013,"21-01-01 12:00",2,2,37},
{4014,"21-01-01 13:00",2,2,35},
{4015,"21-01-01 14:00",2,2,37},
{4016,"21-01-01 15:00",2,2,36},
{4017,"21-01-01 16:00",2,2,33},
{4018,"21-01-01 17:00",2,2,30},
{4019,"21-01-01 18:00",2,2,31},
{4020,"21-01-01 19:00",2,2,28},
{4021,"21-01-01 20:00",2,2,25},
{4022,"21-01-01 21:00",2,2,22},
{4023,"21-01-01 22:00",2,2,24},
{4024,"21-01-01 23:00",2,2,26},
{4025,"21-01-02 00:00",2,2,24},
{4026,"21-01-02 01:00",2,2,23},
{4027,"21-01-02 02:00",2,2,26},
{4028,"21-01-02 03:00",2,2,25},
{4029,"21-01-02 04:00",2,2,25},
{4030,"21-01-02 05:00",2,2,26},
{4031,"21-01-02 06:00",2,2,30},
{4032,"21-01-02 07:00",2,2,26},
{4033,"21-01-02 08:00",2,2,21},
{4034,"21-01-02 09:00",2,2,23},
{4035,"21-01-02 10:00",2,2,32},
{4036,"21-01-02 11:00",2,2,25},
{4037,"21-01-02 12:00",2,2,25},
{4038,"21-01-02 13:00",2,2,25},
{4039,"21-01-02 14:00",2,2,26},
{4040,"21-01-02 15:00",2,2,26},
{4041,"21-01-02 16:00",2,2,25},
{4042,"21-01-02 17:00",2,2,26},
{4043,"21-01-02 18:00",2,2,26},
{4044,"21-01-02 19:00",2,2,25},
{4045,"21-01-02 20:00",2,2,25},
{4046,"21-01-02 21:00",2,2,24},
{4047,"21-01-02 22:00",2,2,23},
{4048,"21-01-02 23:00",2,2,24}
}
)
Thanks for your help, all hints are welcome!
Upvotes: 1
Views: 565
Answers (1)
Reputation: 4005
I think this question is very interesting and I have been meaning to answer it sooner! If a line chart is required, you can do the following:
- Add a
GENERATESERIEStable of the number of hours you need in your analysis, for example (edited based on your comment):
Hours = GENERATESERIES(0, DISTINCTCOUNT(data[datetimestamp]), 1)
- Add the following measure to calculate your load curve:
Load Curve :=
VAR _hrs = SELECTEDVALUE ( Hours[Value] )
VAR _tbl =
SUMMARIZE(
data ,
data[datetimestamp] ,
"Power", SUM ( data[point_value] )
)
VAR _power_tbl =
ADDCOLUMNS (
SUMMARIZE (
_tbl ,
[Power]
),
"Count" ,
VAR _val = [Power]
RETURN
COUNTROWS (
FILTER (
_tbl ,
[Power] >= _val
)
)
)
RETURN
SUMX (
FILTER ( _power_tbl , [Count] = _hrs ) ,
[Power]
)
Use this measure with your Hours table column (by default called Value) to establish your dynamic load curve:
Upvotes: 2
Related Questions
- Show time duration as "hh:mm:ss" in PowerBI charts
- Calculating Durations in Power Bi
- Power BI Dax Calculate Time Intervals With Complicated Logic
- Is there a way to fix the x axis (time) in a certain range in Power BI?
- Average Duration DAX Power BI
- How to find total duration of time in power bi?
- PowerBI Rate Of Change
- How do I get state duration in DAX?
- PowerBI - Cumulative Measures
- Working with Duration (Availability) in PowerBi