cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SWA_Price Frequent Visitor
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
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
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 Regular Visitor
Regular Visitor

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
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
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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors