cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

How can I make Power Bi do this on the fly: in Excel its 2 sumif statements then subtract them?

So I had to change the info since its confidential but this should be close.  I want to know How much is still in Atlanta so the Destinations are positive numbers and the Source are negative numbers.  Atlanta = +1500 - 1000 +1000 -75 +75 = 1500.  This is only a small percent of the entire dataset so I want Power Bi to do this on the fly where I don't have to say = Atlanta.  I just want it to do it.  How can I do that?  Say I have 1 Million Destinations and Sources, how could I do it without doing it manually?

DestinationDest_dollarsSourceSource_dollars
Atlanta1500Seattle500
Bank500Atlanta1000
Atlanta1000Orlando3000
Bank3000Atlanta75
Atlanta75Seattle80
Bank80Birmingham1500
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: How can I make Power Bi do this on the fly: in Excel its 2 sumif statements then subtract them?

First, you create a dimension City and put all the cities found in both columns into it. Of course, only unique values must reside in the dimension. Then you have 2 fact tables: Destinations and Sources. Destinations is the first 2 columns. Sources - the 2 others. Now you connect the dimension City to the two facts. Filtering is City 1:* Destinations/Sources. This way you can slice by cities anyway you want.

 

Now you create 2 measures:

[Destination Dollars] = SUM( Destinations[Dest_dollars] )

[Source Dollars] = SUM( Source[Source_dollars] )

 

Your measure is:

[Leftover Dollars] = [Destination Dollars] - [Source Dollars]

 

Best

D

 

 

View solution in original post

4 REPLIES 4
Highlighted
Helper I
Helper I

Re: How can I make Power Bi do this on the fly: in Excel its 2 sumif statements then subtract them?

Hey @CW112358 ,

 

You can do a calculated measure: Atlanta Net = calculate(sum(dest_dollars),Destination = "Atlanta")-calculate(sum(source_dollars),Source = "Atlanta")

 

To explain what you're doing, you're summing all destination dollars that Destination = Atlanta and subtracting all source dollars for Source = Atlanta.

 

-Paul

Highlighted
Helper II
Helper II

Re: How can I make Power Bi do this on the fly: in Excel its 2 sumif statements then subtract them?

That's one by one though, how can I make it general so that if I have 1 Million destinations that I want totals for, I don't have to write that many calcs?

Highlighted
Helper I
Helper I

Re: How can I make Power Bi do this on the fly: in Excel its 2 sumif statements then subtract them?

Ah, I would just do some new summarized table then...

 

New Dest Table = SummerizeTable('Table Name',[Destination],"Destination Sum",sum(Destination Value))

New Source Table = SummerizeTable('Table Name',[Source],"SourceSum",sum(Source Value))

 

If you don't have a master list of locations, and if all locations (Source and Destination are in the Destination column...

New Dest Table = SummerizeTable('Table Name',[Destination])

 

then do two new calculated columns where you lookup the Destination Sum and SourceSum

Destination Sum = Lookupvalue("Destination Sum",'New Dest Table'[Destination],'Current Table'[Destination])

SourceSum = Lookupvalue("SourceSum",'New Source Table'[Source],'Current Table'[Source])

Highlighted
Anonymous
Not applicable

Re: How can I make Power Bi do this on the fly: in Excel its 2 sumif statements then subtract them?

First, you create a dimension City and put all the cities found in both columns into it. Of course, only unique values must reside in the dimension. Then you have 2 fact tables: Destinations and Sources. Destinations is the first 2 columns. Sources - the 2 others. Now you connect the dimension City to the two facts. Filtering is City 1:* Destinations/Sources. This way you can slice by cities anyway you want.

 

Now you create 2 measures:

[Destination Dollars] = SUM( Destinations[Dest_dollars] )

[Source Dollars] = SUM( Source[Source_dollars] )

 

Your measure is:

[Leftover Dollars] = [Destination Dollars] - [Source Dollars]

 

Best

D

 

 

View solution in original post

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors