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.

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. 

Highlighted
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.

Top Solution Authors
Top Kudoed Authors