Calculate average across all categories without showing empty categories

[Anonymous]

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!

[AntrikshSharma]

@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:

[Anonymous]

@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.

[amitchandak]

@Anonymous , Try if this can work

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

[Anonymous]

@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... 🙂