DAX Calculate Average when all categories are present

Question

I have a table with three columns

[date],
[category_1],
[category_2],
[value]

I would like to plot a line chart with [date] in the X axis, [value] in the Y-axis and [category_1] on the legend. I would like the [value] to be averaged over the [category_2] but to be presented only for the [date] and [category_1] values for which ALL [category_2] members are present. If for a given combination of [date] and [category_1] not all types of [category_2] are present then I wouldn't like the average to be presented in the visual.

I understand I should probably use a measure in which the Average is calculated applying a filter in which the number of [category_2] members is equal to the total but so far I haven't found the correct DAX expression. Happy to consider other approaches if possible without creating a DAX measure.

As example, with the following input table:

The resulting measure would be:

There is no result for the last 3 rows because they don't represent both [category_2] values

This would be much easier to answer if you had some sample input data and desired output. An mcve goes a long ways to getting an answer. — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2018-05-29T21:33:21

One way to do this would be to have your measure throw a blank if not all values of Category_2 are present.

myMeasure = IF(DISTINCTCOUNT(Table1[Category_2]) =
                   CALCULATE(DISTINCTCOUNT(Table1[Category_2]), ALL(Table1)),
               AVERAGE(Table1[Value]),
               BLANK())

This gives the average if the distinct count of the Category_2 values in the local filter context matches the distinct count of Category_2 over the entire table, otherwise, it gives a blank.

DAX Calculate Average when all categories are present

1 Answers