Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculate average across all categories without showing empty categories

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!

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try if this can work

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.