Forum Discussion

Harry_Tran's avatar
Harry_Tran
Icon for Helper III rankHelper III
3 years ago

All / Allexcept not working properly

Hi,

I have a table with columns like below:

I have all page filter for filter 1,2,3,4, and 5

 

Measure: Sale = Sum([Sale_Amt])

                TotalSale = CALCULATE ( [Sale], ALL ( Buckets))

                % = Sale / TotalSale

I also try:

               TotalSale = CALCULATE ( [Sale], ALLEXCEPT ( table, filter 1, filter 2, filter 3, filter 4, filter 5))

          Row: State

                   Buckets

          Column: Date

          Values: Sale

                      TotalSale

                       % 

I tryto do is create a TotalSale not affect by Bucket.

But when I filter on the slicer, the measure TotalSale (All) changes. 

For the Allexcept measure, if I remove the filter 3 and 5 from the measure it works but the number is not euqal to save. If I added filter 3 and 5 back to the measure, the number is correct, but for the line level (by bucket), the number is breakdown and not equal to the total level. for example:

 

What I need is

Sale = TotalSale ( at State level)

TotalSale is equal for all bucket

If I use slicer to filter to specific bucket, the TotalSale would be the same and should not be affected.

 

Thank you

1 Reply

  • Harry_Tran ,

     

    Measure: Sale = Sum([Sale_Amt])

    TotalSale = CALCULATE ( [Sale], ALL ( ))

     

    or

     

    TotalSale = CALCULATE ( [Sale], ALLSELECTED ())