Sum a measure within a table

[Anonymous]

Hello,

I am working on creating an interactive report to show which of our cost centers spent more per device than the average on a monthly basis. I am using a measure called the Average Annual Total Cost which is always changing due to input from the user. I want to create a table visual that will show how many months in the last 12 that a cost center has a total cost above the Average Annual Total Cost. I created an indictor using another measure that is either 1 if the cost center's total cost is greater than the annual average and 0 otherwise. However, when I try to sum the indicator measure I find out that I not able to sum a value that is not in a column. Can anyone help me solve this dilema?

What I have:

Cost Center	PO Month	PO Year	Total Cost	Indicator (measure not present in data)
1234	10	2019	$         3,070	1
1234	11	2019	$         7,175	1
4567	1	2020	$         1,609	0
4567	2	2020	$         4,924	1
8910	10	2019	$         1,729	0
8910	11	2019	$         1,514	0
1112	11	2019	$         3,071	1
1112	10	2019	$         8,426	0
1112	12	2019	$         3,034	1

Table I want to build in a visual:

Cost Center	Number of times above average (sum of indicator)
1234	2
4567	1
8910	0
1112	2

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

This is a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

For your case, just create a new measure as below:

Measure = 
var _table=SUMMARIZE('Table','Table'[Cost Center],'Table'[PO Year],'Table'[PO Month],"_value",[Indicator]) return
SUMX(_table,[_value])

Regards,

Lin

[amitchandak]

@Anonymous ,

Try like

sumx(summarize(table,table[Cost Center],table[PO Month],,table[PO Year],"_sum",[Indicator]),[_sum])

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

So probably something like:

Measure = 
  SUMX(
    ADDCOLUMNS(
      'Table',
      "__Indicator",[Indicator]
    ),
    __Indicator
)

Put that measure in you visual along with Cost Center.