Forum Discussion

mnarmeen's avatar
mnarmeen
Advocate I
9 years ago
Solved

All function with condition

Hello,

 

I have a date filter which I want to ignore on one column of the table, I cant use edit visualization for the whole table as other columns depends on date.

 

I want to show total sum of bottles a user owns which is irrespective of the date. But the problem is it interacts with date filter and only shows bottles which are bought by the user in selected month. So far I have tried using the following formula for making a calculated column, but it doesn't give desired result.

 

TotalSale=CALCULATE(SUM(entries[itemQty]),FILTER(ALL(entries),entries[itemCode]="19LBottle"))

 

I have also used another method to solve it,l but still it doesnot provide me with correct results

 

Column1=CALCULATE(SUM(entries[itemQty]),entries[itemCode]="19LBottle"))

Column2=CALCULATE(SUM(entries[itemQty]),ALL(Column1)))

 

 

Any help would be appreciated.

 

Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    9 years ago

    Hi mnarmeen,

     

    As a good practice I ussually do a Calendar table to filter out my reports, however on your measure you are using all in the full table you need to only do the ALL for the date column you can try to make this change to your measure:

     

    TotalSale =
CALCULATE (
    SUM ( entries[itemQty] ),
    FILTER ( ALL ( entries[Date] ), entries[itemCode] = "19LBottle" )
)

     

    Regards,

    MFelix

     

     

9 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    Hi mnarmeen,

     

    You need to calculate this as a measure and not as a column, if you make your first formula in a measure and add it to your visuals should work, the ALL is a formula that is based on context so when you used it in a column the context is the row itself so no "filters" are applied but when you add that column to your reports the filter then kicks in and filter out the values that you also don't want.

     

    Regards

    MFelix

    • mnarmeen's avatar
      mnarmeen
      Advocate I
      9 years ago

      Thankyou MFelix , 

       

      Ive made this a measure in enteries table , but still it wont show me the values. It only shows me values when I choose ALL from my date slicer.

       

       

      TotalSale=CALCULATE(SUM(entries[itemQty]),FILTER((entries),entries[itemCode]="19LBottle"))

    • mnarmeen's avatar
      mnarmeen
      Advocate I
      9 years ago

      And if I use this, it show me all the Qty without filtering it on the basis of customerid, which is present in the table

       

      TotalSale=CALCULATE(SUM(entries[itemQty]),FILTER(all(entries),entries[itemCode]="19LBottle"))

      • MFelix's avatar
        MFelix
        Super User
        9 years ago

        Is the date filter on the same table or on a Calendar table?

         

        Regards,

        MFelix