Sumif between two tables

[Ragnar_Raghu]

Hi All,

I have two Tables

Table 1 has hourly price of electricity for each day

Date               hour-block   Price

01/04/2017     0 to 1            200

01/04/2017     1 to 2            400

02/04/2017    0 to 1             500

02/04/2017    1 to 2             100

02/04/2017    2 to 3             900

03/04/2017    0 to 1             152

03/04/2017    1 to 2             152

03/04/2017    2 to 3             152

04/04/2017    0 to 1             168

04/04/2017    1 to 2             789

04/04/2017    2 to 3             852

Table 2 has units of electrictity traded hourly each day

Date               hour-block   units traded

01/04/2017     0 to 1            200

01/04/2017     1 to 2            400

02/04/2017    0 to 1             500

02/04/2017    1 to 2             100

02/04/2017    2 to 3             900

03/04/2017    0 to 1             152

03/04/2017    1 to 2             152

03/04/2017    2 to 3             152

04/04/2017    0 to 1             168

04/04/2017    1 to 2             789

04/04/2017    2 to 3             852

I need to plot a graph which shows that price and units traded like below

Price Range     Units traded

below 100        0

100- 200          298

201 to 300      456

301 to 400      963 and so on

request your help

Regards

[Ashish_Mathur]

Hi,

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

[baronraghu]

Hi Ashish,

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

Price Range    Units traded

 below 100       100

101 to 200      824

201 to 300       0

301 to 400       400

401 to 500      500

above 501      2541 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[baronraghu]

Hi Ashish,

Thanks for the solution.

Just wanted to understand, How did you merge the tow tables- price and unit traded. I used inner join function but was unable to get it

[Ashish_Mathur]

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

[baronraghu]

Hi Ashish,

I did the Full join merging and found similar column from table 2 getting also added in the merged table. Could you please share the trick. Tje sample pbi file u shared does have any duplicate columns (date/hour block)

I tried to remove the columns but it gtends to impact the data set in the merged table

[Ashish_Mathur]

Hi,

These images should clarify

[baronraghu]

thanks ashish... hope this should work