Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Ragnar_Raghu
New Member

Sumif between two tables

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

 

 

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

 

 

Hi,

These images should clarify

Untitled.pngUntitled1.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

thanks ashish... hope this should work

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.