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
Anonymous
Not applicable

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
Cmcmahan
Resident Rockstar
Resident Rockstar

You were right in realizing trying to control this by the location columns is a hassle you shouldn't have to deal with.

 

I ignored that column, and unpivoted your data.  From there, I was able to see that you were running into the classic problem of wanting to display something differently in the matrix's total column compared to the others, so was able to create a simpler measure to use in all parts of the matrix:

Approved Budget = IF(ISINSCOPE(Sheet1[Budget Location]), SUM(Sheet1[Budget Amount]), CALCULATE(SUM(Sheet1[Budget Amount]), ALLEXCEPT(Sheet1, Sheet1[Lead Name])))

think this solves the issue you are trying to solve?  You seem to have some sort of other requirement for displaying the Lead Name concatenated with the locations it aligns with?  I've left the original Location column in the data model so you can use it as you see fit. https://drive.google.com/open?id=1WPsbDSQhGDsdPJ0Yw1Q_OxIhhpgjCy7S

 

I just took a look at @Ashish_Mathur 's solution, and he did the same thing I did, except removed the unnecessary comma-separated value of Location.  He also used a simpler calculation for Approved Budget Amount, that only sums the currently filtered budget amounts.  Mine ignores filters and shows the entire lead's Approved Budget when the measure is being calculated in the context of a total row in a matrix.

 

You seemed to have an issue with the fact that unpivoting duplicates the lead name in your tables, and how that would affect your final visual.  What sort of visual do you want to show this in once it is complete? It's my assumption that you would just aggregate each budget amount by the Lead Name, and that would lead to a single value for each Lead Name.  If this isn't the case, please let us know how you eventually intend to display this information.

Ashish_Mathur
Super User
Super User

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/
Anonymous
Not applicable

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

Hi,

Review the approved budget measure.


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

Yes, I can read the simple DAX. I don't see how that answers my concern. I don't mean to be rude, but if you aren't goign to take the time to explain your solution, then why reply?

Hi,

Neither do i mean to be rude but please learn by self-reading.  With limited time that i have (as is also the case with you), I'd rather answer more questions than "spoon feed" others.


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

Hi @Anonymous , 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.

Anonymous
Not applicable

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

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k  Did you ever figure out a solve?

@Anonymous hey sorry for not getting back on this. I thought @Ashish_Mathur already resolved it. What is the issue with the solution he provided? 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k  It looks liek his solution splits the records into new rows, and somehow(still unexplained) manages not to duplicate the other values of the record while doing it. I don't understand how he did that. I coule be missing something simple, but he offered no further explanation.

 

Also, the the final product should still display multiple locations values if the record has them. I can't show:

Channel 1 IE

Channel 1 LA

 

It would need to be

 

Channel 1 IE,LA

Anonymous
Not applicable

I'm still looking for a solve on this one if anyone can help.

Anonymous
Not applicable

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.

@Anonymous sure send the file when ready.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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

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.