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!!!!!!
Solved! Go to 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).
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])
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
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).
User | Count |
---|---|
452 | |
64 | |
54 | |
47 | |
46 |