topic Calculate average across all categories without showing empty categories in DAX Commands and Tips

Calculate average across all categories without showing empty categories

Anonymous — Wed, 28 Oct 2020 14:58:57 GMT

Hi all,

I am using a visual to show average times per category (bars) as well as an overall average across ALL categories (dotted line). Please see screenshot below.

I am using a Measure to calculate the overall average (dotted line):

Overall Avg = CALCULATE(SUM('Table1'[Work Time])/COUNT('Table1'[Number]),ALLSELECTED('Table1'))

The calculation works fine. However, the problem is that the ALLSELECTED() function always returns also empty categories (see Category D). Especially when there are more empty categories, this looks really ugly.

I already tried using ALL(), ALLEXCEPT(), etc. instead of ALLSELECTED() without success. Also, "Show items with no data" is disabled.

Any ideas how to fix that? Thanks a million!

Re: Calculate average across all categories without showing empty categories

amitchandak — Wed, 28 Oct 2020 15:29:48 GMT

Anonymous , Try if this can work

Overall Avg = CALCULATE(SUM('Table1'[Work Time])/COUNT('Table1'[Number]),filter(ALLSELECTED('Table1'), not(isblank(Table[category]))))

Re: Calculate average across all categories without showing empty categories

Anonymous — Wed, 28 Oct 2020 15:42:49 GMT

amitchandak Thank you for your fast reply.

Unfortunately, this doesn't work as well. Same result.

In addition, I want to use this Measure for different "Category" columns. Hence, it should be as generic as possible and I intended not to use the "Category" column itself in the Measure. It might be possible workaround of course... 🙂

Re: Calculate average across all categories without showing empty categories

AntrikshSharma — Wed, 28 Oct 2020 16:06:11 GMT

Anonymous Create a separate measure for total work time:

Total Work Time = SUM ( 'Table1'[Work Time] )

The try this:

Overall Avg = CALCULATE ( DIVIDE ( [Total Work Time], COUNT ( 'Table1'[Number] ) ), FILTER ( VALUES ( 'Table'[Category] ), [Total Work Time] <> 0 ) )

Here is a visual from my dummy data:

Re: Calculate average across all categories without showing empty categories

Anonymous — Thu, 29 Oct 2020 09:37:01 GMT

AntrikshSharma Thank you for your help.

If I use your approach, I get the average value per category instead of an overall average across the categories (see below). The empty category is not visible now!

Not sure if this is important, but the value of my cateogries is not actually 0. Instead, the "empty" categories are hidden due to some applied filters.