Printing 0-values with summarize

[Philip-K]

I use one large data file in which all incidents are reported. Both the incident and the impact of the incident is reported in this source file. To calculate the moving average over several months, I use the SUMMARIZE function to create a new table. In this table I either count the IDs summarized by YearMonth column, or I SUM the impact of the incidents summarized by the YearMonth column. Then I proceed calculating the moving average.

The issue I'm having is the following: When a month has 0 incidents, it is not recorded as a value. I would like to summarize all the months and print a 0-value for months in which no incidents occured (with the specific filter).

Here is the formula I use to summarize the data:

M2.1 = SUMMARIZE(CALCULATETABLE(Incidents;Incidents[Category]="D3";Cause[Scope]=1;'Impact level'[Impact level]<=4);Incidents[YearMonth];"Summed impact per incident";SUM(Incidents[Impact]))

The Date column:

Date = DATE(LEFT('M2.1'[YearMonth];4);RIGHT('M2.1'[YearMonth];2);1)

And to calculate moving average:

Moving average =
var startdate=Min('M2.1'[Date])+334 
var date = 'M2.1'[Date] 
var d365 = EDATE(date;-12) 
var r=
IF(date>=startdate; 
AVERAGEX( FILTER('M2.1'; 'M2.1'[Date] <= date && 'M2.1'[Date] > d365); 
'M2.1'[Summed impact per incident] )
)
RETURN
r

Any help is appreciated!

[v-caliao-msft]

Hi @Philip-K,

Please elaborate your data model structure and provide us some sample data, so that we can make further analysis.

Regards,

Charlie Liao

[Philip-K · Accepted solution]

Hi,

I recently figured out how to solve the problem I was having. If I break it down, the real problem was that I wanted the table I create (as a summary of all the data in the master data file) did not show the months in which no data was recorded. So what I did is the following:

I created a static table with all the YearMonth combinations for the forseeable future and recorded past. (i.e. 2013-01 untill 2050-12)

Then I created a new calulated table with the DAX: Distinct(YearMonth) to print every individual value into this table.

Following I created an IF calculation on the SUM (and separately COUNT) of the incidentents in the following way:

IF(CALCULATE(SUM(<incidents>);<filters>)=blank();0;

CALCULATE(SUM(<incidents>);<filters>))

It works perfectly in this situation. Because this situation called for a moving average calculation over several fillers on data, I couldn't find a way to use the measure functionality. I would love to see the moving average be a feature in the visualization level in some future release. My opinion on the matter is that this should be Chart-functionality, not something I need to calculate myself.

[hohlick]

Hi @Philip-K

You can make it shorter, I think:

CALCULATE(SUM(<incidents>);<filters>)+0

[Philip-K]

Great addition, thanks!