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

[CW112358]

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?

Destination	Dest_dollars	Source	Source_dollars
Atlanta	1500	Seattle	500
Bank	500	Atlanta	1000
Atlanta	1000	Orlando	3000
Bank	3000	Atlanta	75
Atlanta	75	Seattle	80
Bank	80	Birmingham	1500

[Anonymous · Accepted solution]

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

[Anonymous]

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

[CW112358]

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?

[Anonymous]

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])