locked
ISBLANK() not returning a value RRS feed

  • Question

  • I am trying to do something I thought would be very simple.  I have a PowerPivot table that displays sales quantites for organizations by date. The source PowerPivot table does not have a record for each org/day combo, so the resulting pivot table has blank quantities for organizations on dates without orders.  Ultimately what I am trying to do is get an average that includes each organization displayed, not just those that have values (see average wanted row below). 

    1/1/2012

    1/2/2012

    1/3/2012

    Quantity

    ISBLANK

    Quantity

    ISBLANK

    Quantity

    ISBLANK

    Org 1

    3

    FALSE

    21

    FALSE

    30

    FALSE

    Org 2

     

     

    3

    FALSE

     

     

    Org 3

    9

    FALSE

    6

    FALSE

     

     

    Total

    12

     

    30

     

    30

     

    Average Returned

    6

     

    10

     

    30

     

    Average Wanted

    4

     

    10

     

    10

     


    My first thought was to replace the blanks with zeros so that the average would calculate as I want.  When I set the PivotTable option to show empty cells as 0, this only has an effect on the display and does not change the averages calculated.  I also tried adding a measure to check for blank values =ISBLANK([Quantity]).  I may be missing something, but it only returned FALSE, never TRUE for the empty cells.

    I can get a count of the total number of organizations and divide the total sales for each day by that number, but I also want to be able to filter the PivotTable by certain criteria that would change the number of organizations displayed and thus the total number of organizations that should be included in the average.

    Any help would be greatly appreciated!


    • Edited by mcdc Saturday, February 11, 2012 2:01 AM
    Saturday, February 11, 2012 1:58 AM

Answers

  • correct. if you must clear the filter context only one column you can still use ALL() - instead of passing the table you can pass a column name.  Or if you want to keep the filter context only on one column you can use ALLEXCEPT().  that would give you the total count distinct of organizations for the organization-type in the current filter context

    CALCULATE(COUNTROWS(DISTINCT(DimOrganization[Organization]) ), ALLEXCEPT(DimOrganization[OrganizationType]))




    Javier Guillen
    http://javierguillen.wordpress.com/

    • Marked as answer by mcdc Sunday, February 12, 2012 11:46 PM
    Sunday, February 12, 2012 6:39 PM
    Answerer
  • CALCULATE(COUNTROWS(DISTINCT(DimOrganization[Organization]) ), ALL(DimOrganization[SalesDate]))

    Okay, so I think that the above will work for me (including the SalesDate column to clear only that filter).  I can still add new filters to the PivotTable without modifying the calculation. 

    Thank you so much!

    • Marked as answer by mcdc Sunday, February 12, 2012 11:45 PM
    Sunday, February 12, 2012 11:45 PM

All replies

  • Hi mcdc,

    Have you tried using a distinct count expression in the denominator, clearing the current filter context?

    Something like the expression below would give you the count of all your organizations regardelss if they have a quantity value or not.  

    CALCULATE(COUNTROWS(DISTINCT(DimOrganization[Organization]) ), ALL(DimOrganization))




    Javier Guillen
    http://javierguillen.wordpress.com/

    Sunday, February 12, 2012 1:54 AM
    Answerer
  • Thank you for your reply!  I have tried something similar, but the above sample completely clears all filters on the current context, is that correct?  What I would like is that if I add a filter, like organization type, which then limits the list from three to two orgranizations that my average would calculate using a count of two instead of three.   

    Sunday, February 12, 2012 5:09 PM
  • correct. if you must clear the filter context only one column you can still use ALL() - instead of passing the table you can pass a column name.  Or if you want to keep the filter context only on one column you can use ALLEXCEPT().  that would give you the total count distinct of organizations for the organization-type in the current filter context

    CALCULATE(COUNTROWS(DISTINCT(DimOrganization[Organization]) ), ALLEXCEPT(DimOrganization[OrganizationType]))




    Javier Guillen
    http://javierguillen.wordpress.com/

    • Marked as answer by mcdc Sunday, February 12, 2012 11:46 PM
    Sunday, February 12, 2012 6:39 PM
    Answerer
  • CALCULATE(COUNTROWS(DISTINCT(DimOrganization[Organization]) ), ALL(DimOrganization[SalesDate]))

    Okay, so I think that the above will work for me (including the SalesDate column to clear only that filter).  I can still add new filters to the PivotTable without modifying the calculation. 

    Thank you so much!

    • Marked as answer by mcdc Sunday, February 12, 2012 11:45 PM
    Sunday, February 12, 2012 11:45 PM