sum by category is dynamic based on filter of other category

[mkeiper]

Let's call this "Table Cost":

Year	Type	Acc #	Cost
2015	A	1	$1,000
2015	A	1	$500
2015	B	1	$2,000
2015	A	2	$200
2015	B	2	$500
2015	C	2	$3,000
2015	D	2	$1,000
2015	A	3	$1,500
2015	A	3	$600
2016	B	4	$80,000
2016	C	4	$7,000
2016	C	5	$750
2016	A	6	$2,500
2016	D	6	$2,200
2016	D	6	$1,600
2016	A	7	$300
2016	A	7	$45,000
2016	A	7	$500
2016	B	7	$2,600
2017	A	8	$4,200
2017	B	8	$400
2017	C	8	$36,000
2017	A	9	$8,500
2017	C	9	$3,200
2017	C	10	$70,000
2017	D	10	$500
2017	D	10	$3,000
2017	A	11	$200
2017	A	11	$1,000

I have created a SUM of Cost by Acc # using above data called "Table Total":

Acc #	Total Cost
1	$3,500
2	$4,700
3	$2,100
4	$87,000
5	$750
6	$6,300
7	$48,400
8	$40,600
9	$11,700
10	$73,500
11	$1,200

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:

1	$3,500
2	$700
3	$2,100
4	$80,000
6	$2,500
7	$48,400
8	$4,600
9	$8,500
11	$1,200

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

I need help!!!!!!

[stretcharm]

I'm not sure if this is what you want.

I added a measure like this to calc your layers from the "Table Cost" and filter by the Type.

MeasureLayerCost = 
VAR Total =
    CALCULATE ( SUM(  'Table Cost'[Cost] ) )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( Total ), BLANK (),
        Total < 1000, "1k",
        Total < 3000, "3k",
        Total < 81000, "81k",
        "over81k"
    )

If you want a summary you can create a summarised table with the New Table option and

Table Total =
ADDCOLUMNS (
    SUMMARIZE ( 'Table Cost', 'Table Cost'[Acc #], 'Table Cost'[Type] ),
    "Total", CALCULATE ( SUM ( 'Table Cost'[Cost] ) )
)

This can then be used for binning from a total or you can add a similar Layer Calc

If you need a Dimension table of types to join to the Cost and Total so you can have a single filter then you can create a distinct list using this again for a New Table.

Types = DISTINCT('Table Cost'[Type]) 

[mkeiper]

Thanks for the reply.

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

[stretcharm · Accepted solution]

If you don't have Acc # in the table/matrix it will sum up by the measure Layer.

You can also remove the Acc # friom the grouping on the summary table

Table Total = ADDCOLUMNS(SUMMARIZE('Table Cost', 'Table Cost'[Type]), "Total", CALCULATE(SUM('Table Cost'[Cost])) )

MeasureLayerTotal = 
VAR Total =
    CALCULATE ( SUMX ( 'Table Total', 'Table Total'[Total] ) )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( Total ), BLANK (),
        Total < 1000, "1k",
        Total < 3000, "3k",
        Total < 81000, "81k",
        "over81k"
    )

If this is not what you want can you show the end result when filtering the type(s).