Forum Discussion
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])))
I 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.