cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

sum by category is dynamic based on filter of other category

Let's call this "Table Cost":

YearTypeAcc #Cost
2015A1$1,000
2015A1$500
2015B1$2,000
2015A2$200
2015B2$500
2015C2$3,000
2015D2$1,000
2015A3$1,500
2015A3$600
2016B4$80,000
2016C4$7,000
2016C5$750
2016A6$2,500
2016D6$2,200
2016D6$1,600
2016A7$300
2016A7$45,000
2016A7$500
2016B7$2,600
2017A8$4,200
2017B8$400
2017C8$36,000
2017A9$8,500
2017C9$3,200
2017C10$70,000
2017D10$500
2017D10$3,000
2017A11$200
2017A11$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
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).

 

 

View solution in original post

3 REPLIES 3
Highlighted
Memorable Member
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 =
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]) 

 

Highlighted
Frequent Visitor

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

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

Highlighted
Memorable Member
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).

 

 

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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