Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
CW112358
Helper II
Helper II

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

‎02-26-2020 10:40 AM

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
Labels:
Message 1 of 14
1,528 Views
0
1 ACCEPTED SOLUTION
richbenmintz
Solution Sage
Solution Sage
In response to CW112358

‎02-27-2020 08:04 AM

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

Message 11 of 14
1,484 Views
13 REPLIES 13

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

View All