I have created a SUM of Cost by Acc # using above data called "Table Total":
My goal is to be able to have "Total Cost" be dynamic based of the filtering of "Type" in the "Table Cost". If I were to select "Type A" and "Type B" my "Total Cost" Table would change to:
showing only SUM(Cost) by Acc # when Type = A or B.
The "Type" filter will change and is applied to Page.
Furthermore, I would like to take the "Total Cost" and create new Calculations that Layer the total costs (0-10K, 10K-25K, 25K-50K, etc). These layers would then change based on the types selected.
I have already tried creating a query that groups Acc # summing the cost (Table Total) and creating a relationship with "Table Cost". ---"Total Cost" does not change based on filter of Table Cost[Type]
I have tried to create "Table Total" by:
Total Cost= SUMX(RELATEDTABLE(Table Cost), Table Cost[Cost])
connecting table with distinct Acc #--- Numbers are even close to what I want
I have also tried creating a measure in "Table Cost":
Total Cost= CALCULATE(SUM(Cost), ALLEXCEPT(Total Cost, [Acc #])---- This creates an Acc # for ALL YEARS (Acc #1 is in 2015,2016,2017) when making a table and therefore if I want to make a graph by year, the numbers are all equal being the SUM TOTAL
The biggest thing for this problem is the need for the Acc # costs to be summed up in one row. This one row would then change Total Cost based on filters of Type... I don't think your summary solution would work because the rows would have Acc # and Type which will be an issue as Acc # would end up in multiple rows. Unless I am misinterpreting what you are saying