How do I calculate proportional values depending on multiple variables? - Rainfall data

[OllieSvT]

I am attempting to create virtual rainfall data for multiple 'sites' using varying proportional combinations of three rain gauges. 

My data are held in a few tables/lists in the following formats:

Site list:

Station apportionment table (not yet processed, no data types selected etc):

Daily rain fall data (summarised table from hourly data):

I would like to define rainfall values in the format:

Rainfall at site z = (rainfall at site z gauge 1 * proportion of gauge 1 for site z) + (rainfall at site z gauge 2 * proportion of gauge 2 for site z) + (rainfall at site z gauge 3 * proportion of gauge 3 for site z)

I would like to have daily values to plot up whereby depending on a (single-select) site slicer the appropriate "virtual rainfall data" are plotted.

The current data model is small 1 year of daily data, ~40 sites and 16 rain gauges. If I can find a clean solution I will look to expand this to 150+ sites and 10+ years of rainfall data (potentially at an hourly resolution).

I would prefer to create this using DAX, but could potentially use a Power Query solution that creates a new rainfall dataset which I can directly link to the 'site' list. For a DAX solution, I can't work out how to relate the site list to the gauge apportionments (do I need to unpivot the second table to 3 columns, Site ID, Gauge ID, proportion? Then come up with some calculation that does the above equation?

EDIT:

To elaborate, my main query is: what is the most efficient measure to calculate the 'daily rainfall (mm/day)' for a given site, using the above equation?

For example, at Audley, the three gauges in use are:

Gauge No.	Gauge Proportion
99093	0.625
99179	0.348
3330	0.027

Thus the estimated rainfall for Audley on a given date is:

Audley Rainfall day n = (rainfall at 99093 on day n * 0.625) + (rainfall at 99179 on day n * 0.348)+ (rainfall at 3330 on day n * 0.027) 

As stated above, I need to calculate this for everyday of a ~10 year (hourly) dataset of rainfall, although some gauges do have isolated missing ranges (generally 98%+ complete).

The end goal is a page with a couple plots on it, presenting the rainfall as calculated above against other internal timeseries data. These will be related by date-time and site ID.

Any and all help is very appreciated. Thanks

[DataInsights]

@OllieSvT,

I would start by transforming the Station Apportionment table so it's in a usable format. Copy the steps beginning with UnpivotColumns into your query.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "Xc+9CsIwFAXgV5FMCqU0/2ZUR8GlgkPpENs7SK9tqRbx7U1uqxSXe0JIvpwUBduNNcKbJYxLbrOQRwCEkDmPQ4XhXOYkJbcupJQyHkyNFpRS0XaqtTGsTAq2h7r1iDManRO8+m54rta5x67fRFmEMaFaLx4hSNEyVTozcYtM9FWDvoFZFf9VF8ZckDoKYamjpRtEHfy9v3Xt6gzD4KsJzEz86QUQ4XHtxqElV/46bt3k2y+sjebUUaotweUH",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Source_Name = _t,
        SAP_SITE_ID = _t,
        First_Station_Name = _t,
        Second_Station_Name = _t,
        Third_Station_Name = _t,
        First_Station_Number = _t,
        Second_Station_Number = _t,
        Third_Station_Number = _t,
        First_State_Proportion = _t,
        Second_State_Proportion = _t,
        Third_State_Proportion = _t
      ]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {
      {"Source_Name", type text},
      {"SAP_SITE_ID", Int64.Type},
      {"First_Station_Number", Int64.Type},
      {"Second_Station_Number", Int64.Type},
      {"Third_Station_Number", Int64.Type},
      {"First_State_Proportion", type number},
      {"Second_State_Proportion", type number},
      {"Third_State_Proportion", type number}
    }
  ),
  UnpivotColumns = Table.UnpivotOtherColumns(
    ChangeType,
    {"Source_Name", "SAP_SITE_ID"},
    "Attribute",
    "Value"
  ),
  SplitColumn = Table.SplitColumn(
    UnpivotColumns,
    "Attribute",
    Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false),
    {"Attribute.1", "Attribute.2"}
  ),
  PivotColumns = Table.Pivot(
    SplitColumn,
    List.Distinct(SplitColumn[Attribute.2]),
    "Attribute.2",
    "Value"
  ),
  RemoveColumn = Table.RemoveColumns(PivotColumns, {"Attribute.1"}),
  FilterNull = Table.SelectRows(RemoveColumn, each ([Station_Number] <> null)),
  ChangeType2 = Table.TransformColumnTypes(
    FilterNull,
    {{"Station_Name", type text}, {"Station_Number", type text}, {"State_Proportion", type number}}
  )
in
  ChangeType2

You'll need to provide sample data for the Daily Rainfall table (not a screenshot).

[v-easonf-msft]

Hi, @OllieSvT 

Not fully sure what is your question, can you elaborate a bit more what you are trying to do?

Sample data and expected output would help tremendously.

Best Regards,
Community Support Team _ Eason