Sumif between two tables

Question

Hi All,&nbsp;I have two Tables&nbsp;Table 1 has hourly price of electricity for each day&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;hour-block&nbsp; &nbsp;Price01/04/2017&nbsp; &nbsp; &nbsp;0 to 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20001/04/2017&nbsp; &nbsp; &nbsp;1 to 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40002/04/2017&nbsp; &nbsp; 0 to 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50002/04/2017&nbsp; &nbsp; 1 to 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10002/04/2017&nbsp; &nbsp; 2 to 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90003/04/2017&nbsp; &nbsp; 0 to 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15203/04/2017&nbsp; &nbsp; 1 to 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15203/04/2017&nbsp; &nbsp; 2 to 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15204/04/2017&nbsp; &nbsp; 0 to 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;16804/04/2017&nbsp; &nbsp; 1 to 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;78904/04/2017&nbsp; &nbsp; 2 to 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;852&nbsp;Table 2 has units of electrictity traded hourly each dayDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;hour-block&nbsp; &nbsp;units traded01/04/2017&nbsp; &nbsp; &nbsp;0 to 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20001/04/2017&nbsp; &nbsp; &nbsp;1 to 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40002/04/2017&nbsp; &nbsp; 0 to 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50002/04/2017&nbsp; &nbsp; 1 to 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10002/04/2017&nbsp; &nbsp; 2 to 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90003/04/2017&nbsp; &nbsp; 0 to 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15203/04/2017&nbsp; &nbsp; 1 to 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15203/04/2017&nbsp; &nbsp; 2 to 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;15204/04/2017&nbsp; &nbsp; 0 to 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;16804/04/2017&nbsp; &nbsp; 1 to 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;78904/04/2017&nbsp; &nbsp; 2 to 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;852&nbsp;I need to plot a graph which shows that price and units traded like belowPrice Range&nbsp; &nbsp; &nbsp;Units tradedbelow 100&nbsp; &nbsp; &nbsp; &nbsp; 0100- 200&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 298201 to 300&nbsp; &nbsp; &nbsp; 456301 to 400&nbsp; &nbsp; &nbsp; 963 and so on&nbsp;request your helpRegards&nbsp;&nbsp;

ashish_mathur · Answer

Hi,
You may download my PBI file from here.
Hope this helps.

ashish_mathur · Answer

You are welcome.&nbsp; See the join type in the Query Editor.&nbsp; I have used "All from Both".

ashish_mathur · Answer

Hi,
These images should clarify

ashish_mathur · Answer

Hi,
How did you get the results of 298, 456 etc.?&nbsp; Show the calculation - be very clear.

baronraghu · Answer

Hi Ashish,&nbsp;Thanks for your prompt response. I appologize for the inconvinece , The sum in the summary table has beeen incorrect. I have updated the summary table as below&nbsp;Price Range&nbsp; &nbsp; Units traded&nbsp;below 100&nbsp; &nbsp; &nbsp; &nbsp;100101 to 200&nbsp; &nbsp; &nbsp; 824201 to 300&nbsp; &nbsp; &nbsp; &nbsp;0301 to 400&nbsp; &nbsp; &nbsp; &nbsp;400401 to 500&nbsp; &nbsp; &nbsp; 500above 501&nbsp; &nbsp; &nbsp; 2541&nbsp;

Forum Discussion

Sumif between two tables

8 Replies

Related content

SumIf

sumif not working

Sumif between two tables

SUMIFS

Sumif on multiple tables