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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CW112358
Helper II
Helper II

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

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

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.

 

Star Schema.PNG

 

Hope this helps..



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

13 REPLIES 13
v-juanli-msft
Community Support
Community Support

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
Solution Sage
Solution Sage

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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?

Hi @CW112358 

 

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

 

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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!

 

DestinationSourceDollars
AtlantaSeattle1500
BankAtlanta500
AtlantaOrlando1000
BankAtlanta3000
AtlantaSeattle75
BankBirmingham80

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.

Can you send the formula you tried



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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?

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


I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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.  

PowerBi_NotEqual.PNG

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.

 

Star Schema.PNG

 

Hope this helps..



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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

Hi @CW112358 ,

 

Glad I could help



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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