I am building a Power BI model that estimates the demand and supply of water for a number of industrial sites. Water is transfered between some of the sites.
I have information feeding into the Power BI that helps to estimate the water that is being supplied from one site to another and have successfully built a measure that returns the right number for each site that has a water supply from another site.
What I want to do is write a measure that represents the 'demand' for water from the site that is sending it to the site that has this water as part of its supply. The below might help to explain:
|Site with a 'demand' to supply water to another site||Site with a 'supply' from another site|
|Site A||>>>>>||Site C||E.g. Site A is sending water to Site C|
|Site B||>>>>>||Site D||E.g. Site B is sending water to Site D|
So currently I have a measure that returns:
I want to write a measure that uses the lookup table below:
|Supply Site||Demand Site|
To return the following:
I hope that makes sense. Appreciate any help you might be able to give!
Thanks for the idea @darlove !
Unfortunately my situation is a little more complext than that. The 'volumes' of water in the Power BI file you attached are actually rates of water that are multiplied with a base table that has a production qty over time for each site. E.g.:
Once I introduce this 'baseline' table into your solution I cannot make a relationship between all four tables. Do you have any ideas on how to get around this?
Thanks @darlove .
The way you have set it up makes sense and is nice and clean and I can get it to work on my model using the 'demand site ID' and 'supply site ID' fields. However, what if I want to report the output from a single 'site' field rather than having the 'supplying site ID' and 'demanding site ID' fields?
I currently have a number of other measures that calculate different types of demand for each site and the dashboard is set up to be filtered by each 'site' rather than a 'supply site' vs. a 'demand site'.
For example assuming there is only one year of data and the only case of external water transfer is Site 1 transfering 2L per qty of it's own production to Site 5, I'm looking to be able to produce the following output:
|Site||Qty||External Supply Rate||External Supply||External Demand Rate||External Demand|
Thanks again @darlove .
I was able to get this to work however, there is one more complication I couldn't get around.
Using the model you uploaded, I have a 'site' lookup table that contains some information about each site (long, lat, and some other groupings). This is then linked to the 'Production Qty' table which is actually at a lower level of detail than 'Site'. This by itself isn't a problem in getting your solution to work however, the issue is I am reporting everything (there are a number of other measures) from the 'site' lookup.
Using your Power BI as an example, I've tried to make some modifications to get the table on the 'Page 2' tab to produce the same results by using the 'suppling site ID' field rather than the 'site' field. Hopefully the picture below helps to explain.
I apologise I should have been more thorough in my original explanation...
Hi @darlove ,
This link should include an example of the model I am using - including the work you have done above.
Appreciate any thoughts you may have.
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!