cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## sum by category is dynamic based on filter of other category

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Memorable Member

## Re: sum by category is dynamic based on filter of other category

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

3 REPLIES 3
Highlighted
Memorable Member

## Re: sum by category is dynamic based on filter of other category

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 =
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]) `

Highlighted
Frequent Visitor

## Re: sum by category is dynamic based on filter of other category

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

Highlighted
Memorable Member

## Re: sum by category is dynamic based on filter of other category

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors