Excel Sumif/Subtract in Power Bi on the fly (not line by line)?

[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

[v-juanli-msft]

Hi @CW112358 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[richbenmintz]

Hi @CW112358 

Assuming your table is Called Table:

you can create the following measures:

Destination Dollars = sum('Table'[Dest_dollars])

Source Dollars = 
    if(HASONEVALUE('Table'[Destination]), 
        CALCULATE(SUM('Table'[Source_dollars])*-1, FILTER(all('Table'), 'Table'[Source] = VALUES('Table'[Destination]))),
        BLANK()
    )

$ Left In Destination = [Destination Dollars] + [Source Dollars]

I believe this this do what you want, if not please provide a sample file

Richard

[CW112358]

Thank you for answering, we tried it and it says (Blank).  Any other ideas.

I can't give the real example because its confidential but basically what I want is, with the summed dollars from adding/subtracting

Total_Remaining_Destination    Total_Remaining_Dollars

Atlanta

Orlando

Bank

Seattle

....

Can you use the table I pasted and see if it works?

[richbenmintz]

Hi @CW112358 

the link below provides you the PBIX file is used to validate the measures

https://1drv.ms/u/s!AhCeuF2piSWMg6BEqPu0FaD7Gr_lNw

[CW112358]

Ok that worked, we had one of our variables selected for one of them that shouldn't be.  How about this table?  How would it be different?  We are trying to work out the most optimized way to have the table and do the exercise.  So dollars are all in one column and you only know if they are negative if you find the word "Atlanta" in the Source column.  So all the totals would be the same.  Thanks again for all the help!

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

[CW112358]

Nevermind, it works for that too at least the math does.  What if I wanted Atlanta&Bank =, so the sum of 2?  We tried just dropping it in and using a filter selecting 2 and that didn't work, it said Blank even though just picking one works.

[richbenmintz]

Can you send the formula you tried

[CW112358]

The same formulas you used but then when using the Cards or tables, we drag over the Destination

Destination Dollars = sum('Table'[Dest_dollars])

Source Dollars =

if(HASONEVALUE('Table'[Destination]),

CALCULATE(SUM('Table'[Dest_dollars])*-1, FILTER(all('Table'), 'Table'[Source] = VALUES('Table'[Destination]))),

BLANK()

)

Left = [Destination Dollars] + [Source Dollars]

Do I need to do the same thing to Destination dollars as I do to Source dollars for if there is only one value or if its blank?

[richbenmintz]

Hi @CW112358 ,

Not really sure what you are asking, 

But i think you would want to make a measure that is specific to the use case as the 

Source Dollars = 
    if(HASONEVALUE('Table'[Destination]), 
        CALCULATE(SUM('Table'[Source_dollars])*-1, FILTER(all('Table'), 'Table'[Source] = VALUES('Table'[Destination]))),
        BLANK()
    )

measure returns the sum of all source dollars where the source is equal the destination in the filter/row/table context. If you just changed the Destination Dollars Measure to match the source dollars Measure pretty sure the results would not be correct. Please feel free to edit the Pbix i shared and save, so i can see what you are trying to achieve. Would help me a lot

[CW112358]

Try filtering and selecting 2 (Atlanta and Bank) and have the table, the numbers are not equal?  It's adding the Destination Dollars instead of the Left dollars even though the field selected is Left.  

[richbenmintz · Accepted solution]

Hi @CW112358 ,

So rethinking this, if we re-shape the data and turn the data into a star schema we can simplify all of the formulas. I created three new tables in the model, using some basic Dax create table formulas. Then created simple measures on each fact table, now when you select the City from the Cities table the source dollars and destination dollars aggregate to the shared dimensionality and the $ Left Measure is simply a product of the two. Please review the pbix file.

Hope this helps..

[CW112358]

Thank you!!!!!!!!  That did the trick!  You just saved us days of time.

[richbenmintz]

Hi @CW112358 ,

Glad I could help