Forum Discussion

kchan002's avatar
kchan002
Frequent Visitor
8 years ago
Solved

measure summary

I have created a MEASURE which I am using on a Matrix table sorted by Sales Reps & Date: 

 

# Deals = calculate(COUNTROWS('Opportunity'),
FILTER('Opportunity','Opportunity'[Omit__c] = FALSE()),
FILTER('Opportunity','Opportunity'[ForecastCategory] <> "Omitted"),
FILTER('Opportunity','Opportunity'[StageName] <> "Closed Won"),
FILTER('Opportunity', 'Opportunity'[First_Day_Close_Date__c] = sum(Quota[Month_Year__c])))

 

The number that calculates displays correctly. However, it does not add it up at the end.

I assumed because there are rows with "null".

 

 

Any help would be appreciated! Thanks!

15 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi kchan002,

    Please add 0 in your formula as follows and check if ot works fine.

    Deals = calculate(COUNTROWS('Opportunity'),
FILTER('Opportunity','Opportunity'[Omit__c] = FALSE()),
FILTER('Opportunity','Opportunity'[ForecastCategory] <> "Omitted"),
FILTER('Opportunity','Opportunity'[StageName] <> "Closed Won"),
FILTER('Opportunity', 'Opportunity'[First_Day_Close_Date__c] = sum(Quota[Month_Year__c])))+0


    Best Regards,
    Angelia

    • kchan002's avatar
      kchan002
      Frequent Visitor
      8 years ago

      Hi, 

       

      It seems that it takes care of the null values and places a "0".

      However, the Total still says 0.

       

                                   Deals

      Sales Rep 1             10

      Sales Rep 2              0

      Sales Rep 3              0

                                      0

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        kchan002

         

        Try adding this MEASURE.

         

         

        DealsWithTotal =
IF (
    HASONEVALUE ( TableName[Name_c] ),
    [# Deals],
    SUMX ( ALLSELECTED ( TableName[Name_c] ), [# Deals] )
)
  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    8 years ago

    kchan002

     

    Try with:

    total # deals = SUMX (SUMMARIZE(‘TableName’, ‘TableName’ (Month), [# Deals])

     

    see if that works.

    regards,

    Paul.

    • kchan002's avatar
      kchan002
      Frequent Visitor
      8 years ago

      Tried the measure - now it adds up the row, but not the column.

       

                                  January       February      March....      Total

      Sales Rep 1              1                  2                  0                 3

      Sales Rep 2              1                  0                  0                 1

      Total                        0                  0                  0

       

      Is there a way to do both?

  • Thyago_Rezende's avatar
    Thyago_Rezende
    Icon for Resolver I rankResolver I
    8 years ago

    Hi,

     

    Try to do this:

     

    SUMX(Opportunity,

    calculate(COUNTROWS('Opportunity'),
    FILTER('Opportunity','Opportunity'[Omit__c] = FALSE()),
    FILTER('Opportunity','Opportunity'[ForecastCategory] <> "Omitted"),
    FILTER('Opportunity','Opportunity'[StageName] <> "Closed Won"),
    FILTER('Opportunity', 'Opportunity'[First_Day_Close_Date__c] = sum(Quota[Month_Year__c]))))