cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SWA_Price
Frequent Visitor

Dynamically Sum Columns based on Slicer

Alright, I have several columns:

 

Location

Approved Budget

SD Budget

SF Budget

LA Budget

OC Budget

IE Budget

BFD Budget

 

Approved Budget should be a meausre that sums sd, sf, la, oc, ie, and bfd budget. I'd like to be able to slice Approved Budget by location, so that if IE is chosen as a location, only rows with >0 entries in IE Budget would sho win the table or visual.

 

Here's where I am getting lost, my location field is a comma seperated text field from my database. Pissble entires include:

 

SD

SF

LA

OC

IE

BFD

SD,LA,IE

SD,SF

All

 

I don't even know if it is wise to use the location column at all in this case, but I need the core values to control whatever slicer I have set up.  

 

TLDR, what I'm saying is probably confusing, so what I'm looking for is how to sum my budget columns based on what locations I select.

 

Test Data Link:

 

https://drive.google.com/file/d/1YlfOmeoPFQHI6DTIQjSIwvT7nTzL1bYC/view?usp=sharing
https://drive.google.com/file/d/1kdchVQXMzVPXoF-pTWsvsw9C9D3S1mql/view?usp=sharing

 

This is the DAX for Approved Budget. I'm sure someone could point out a simpler way to do this if they wanted to:

 

"

Approved Budget = sumx(Sheet1,if(iferror(search("SD",Sheet1[Location]),0)>0,Sheet1[SD Budget],0)+if(iferror(search("SF",Sheet1[Location]),0)>0,Sheet1[SF Budget],0)+if(iferror(search("LA",Sheet1[Location]),0)>0,Sheet1[LA Budget],0)+if(iferror(search("OC",Sheet1[Location]),0)>0,Sheet1[OC Budget],0)+if(iferror(search("IE",Sheet1[Location]),0)>0,Sheet1[IE Budget],0)+if(iferror(search("BFD",Sheet1[Location]),0)>0,Sheet1[BFD Budget],0)+if(iferror(search("ALL",Sheet1[Location]),0)>0,Sheet1[SF Budget]+Sheet1[SD Budget]+Sheet1[LA Budget]+Sheet1[OC Budget]+Sheet1[IE Budget]+Sheet1[BFD Budget],0))"

 

 

data sample.PNG

17 REPLIES 17
Super User IV
Super User IV

Re: Dynamically Sum Columns based on Slicer

@SWA_Price can you put sample how your table looks like? 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





SWA_Price
Frequent Visitor

Re: Dynamically Sum Columns based on Slicer

I'll mock up some anonymouse example and post later.

 

@parry2k  I edited op with some dax and a snapshot of the data. I can send you the bi file and spreadsheet too if that would help.

Super User IV
Super User IV

Re: Dynamically Sum Columns based on Slicer

@SWA_Price sure send the file when ready.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





SWA_Price
Frequent Visitor

Re: Dynamically Sum Columns based on Slicer

@parry2k  There's no attachment permissions here? I added drive file links to the op.

Highlighted
mochabits Advocate I
Advocate I

Re: Dynamically Sum Columns based on Slicer

Hi @SWA_Price , you might want to use Power BI's ETL tool to split up the strings in the location column first. It will be easier to create the measure.

SWA_Price
Frequent Visitor

Re: Dynamically Sum Columns based on Slicer

@mochabits split how? Into different columns? What are you imagining? 

Super User IV
Super User IV

Re: Dynamically Sum Columns based on Slicer

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
SWA_Price
Frequent Visitor

Re: Dynamically Sum Columns based on Slicer

@Ashish_Mathur  It looks like you you just split the list values of lcoation into new rows. I've tried this in a couple ways. Unless I do it in my source data, I always end up with duplicated budget numerals, which means that every lead name with multiple locations would have double or triple the budget once combined. 

 

I see that your Approved Budget matches mine, so how did you do that? Assuming I can replicate that in my real data, I woudl then need to group by lead name for display. I can't show duplciate lead names in my tables. 

Super User IV
Super User IV

Re: Dynamically Sum Columns based on Slicer

Hi,

Review the approved budget measure.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

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

Top Solution Authors
Top Kudoed Authors