Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

filters for measures

hi

 

I have created a cumulative sum meassure that calculates order volume cumulative sum.

 

my data contains, <OrderCreationDate>, <order_type>, <licences>

 

and my meassure works on the <licences> column. I plot the measure over time with the line chart and I have certain report-level and page level filters on the <order_type> which excludes certain order types.

 

however, the measure ignores these filters and calculates and plots the sum of all orders regardless of filters. 

 

Cumulative sales = CALCULATE(sum(O365LicenseReport[Licenses]),FILTER(all(O365LicenseReport), O365LicenseReport[OrderCreationDate]<=max(O365LicenseReport[OrderCreationDate])))

 

I even tried to impose visual revel filters and the funny thing is that the order types I want to exclude do not appear in the options because they have been filtered out by the report and page-level filters ????

 

what am I missing here?

Need Help
Show and Tell
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi Anonymous,

     

    If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?

     

    You could try to modify your formula like below, that should be your expected.

     

    Cumulative sales =
CALCULATE (
    SUM ( O365LicenseReport[licences] ),
    FILTER (
        ALLSELECTED ( 'O365LicenseReport' ),
        O365LicenseReport[OrderCreationDate]
            <= MAX ( O365LicenseReport[OrderCreationDate] )
    )
)

    Here is my test output.

     

     

    Hope this can help you!

     

    Best Regards,

    Cherry

  • Anonymous's avatar
    Anonymous
    8 years ago
    v-piga-msft wrote:

    Hi Anonymous,

     

    If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?

     

    You could try to modify your formula like below, that should be your expected.

     

    Cumulative sales =
CALCULATE (
    SUM ( O365LicenseReport[licences] ),
    FILTER (
        ALLSELECTED ( 'O365LicenseReport' ),
        O365LicenseReport[OrderCreationDate]
            <= MAX ( O365LicenseReport[OrderCreationDate] )
    )
)

    Here is my test output.

     

     

    Hope this can help you!

     

    Best Regards,

    Cherry

    YES!!!!!

     

    THANK YOU !!!!!!

     

    yes !!! now it works...  I understand that I should have used the "ALLSELECTED" rather the "ALL" statement right?

     

    if I understand it correctly, ALLSELECTED (as implied) respects filters where as ALL ignores them, correct?

     

12 Replies

  • prakritnepal's avatar
    prakritnepal
    Icon for Helper I rankHelper I
    8 years ago

    You have Filter(all.... in your measure. I believe that is going to return all rows irrespective of visual level filters.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I see

       

      so how do it tell it "respect filters or slicers in the page" ?

       

      i don't want to hardwire the filters in the measure code cause that is not make the measure interactive ( I guess I'm stating the obvious)..

       

       

       

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        8 years ago

         Hi Anonymous,

         

        Have you tried using the quick measure feature to create a cumulative sum?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Can you post a pbix file with expected output?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        unfortunatelly I have actual sales data, can't post the pbix. I will try to recreate with dummy data

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hi Anonymous,

     

    If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?

     

    You could try to modify your formula like below, that should be your expected.

     

    Cumulative sales =
CALCULATE (
    SUM ( O365LicenseReport[licences] ),
    FILTER (
        ALLSELECTED ( 'O365LicenseReport' ),
        O365LicenseReport[OrderCreationDate]
            <= MAX ( O365LicenseReport[OrderCreationDate] )
    )
)

    Here is my test output.

     

     

    Hope this can help you!

     

    Best Regards,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago
      v-piga-msft wrote:

      Hi Anonymous,

       

      If I understand your scenario correctly that you have created a measure Cumulative sales but measure ignores the filters about report-level and page level filters on the <order_type>?

       

      You could try to modify your formula like below, that should be your expected.

       

      Cumulative sales =
CALCULATE (
    SUM ( O365LicenseReport[licences] ),
    FILTER (
        ALLSELECTED ( 'O365LicenseReport' ),
        O365LicenseReport[OrderCreationDate]
            <= MAX ( O365LicenseReport[OrderCreationDate] )
    )
)

      Here is my test output.

       

       

      Hope this can help you!

       

      Best Regards,

      Cherry

      YES!!!!!

       

      THANK YOU !!!!!!

       

      yes !!! now it works...  I understand that I should have used the "ALLSELECTED" rather the "ALL" statement right?

       

      if I understand it correctly, ALLSELECTED (as implied) respects filters where as ALL ignores them, correct?