Showing percentages in line graph

[Taivanbat]

I have a line graph showing the average wind speed in different time intervals during March. I need to show the percentages of wind in which the wind speeds were within specific intervals: 0-5 m/s, 5-10 m/s, and 10-20 m/s. For example, from 12 A.M. to 9 A.M., 40% of the time had wind speeds in the 5-10 m/s interval. How can I show this information in another graph or merge graphs?

[RossEdwards · Accepted solution]

What you are going to need to do is set up a dimensional table for your time of day that will hold that categorization (or multiple types if you want a few different ones).

A Time table that contains the values relevant to your dataset.  I.e. if your data set is down to '5 minute granularity', you could have a table with every 5 minutes.  You can make this table simply using List.Times:

List.Times - PowerQuery M | Microsoft Learn

This dimensional tables then need a column which holds the category text, which handles the grouping up.  Once in your model, link your data table with this Time table.

For grouping up the wind speeds, you could create a measure counts the number of records between X-Y.  I.e.:

0-5ms = CALCULATE(
    COUNTROWS('YourData'),
    'YourData'[WindSpeed] >= 0,
    'YourData'[WindSpeed] <= 5
)

To get a percentage, you'd just use the 0-5ms measure (or similar) divided by the total count of records for that context (i.e. for the timecategory, day, month etc)

i.e.:

0-5ms Pct = DIVIDE([0-5ms], COUNTROWS('YourData'))