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

Re: Dynamically Sum Columns based on Slicer

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?

Super User IV
Super User IV

Re: Dynamically Sum Columns based on Slicer

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/
SWA_Price
Frequent Visitor

Re: Dynamically Sum Columns based on Slicer

@parry2k  Did you ever figure out a solve?

Super User IV
Super User IV

Re: Dynamically Sum Columns based on Slicer

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






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

SWA_Price
Frequent Visitor

Re: Dynamically Sum Columns based on Slicer

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

Super User III
Super User III

Re: Dynamically Sum Columns based on Slicer

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

 

Super User III
Super User III

Re: Dynamically Sum Columns based on Slicer

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.

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