Forum Discussion

Icon for Helper I rank

Helper I

7 years ago

Solved

Grand Total ALL() with filter

Hello,

I have created a measure

Grand Total = CALCULATE(sum(Table1[Resale Qty]),all(Table1[Resale Price]))

when i don't filter Product, everything seems correct. P1

when i filter Product, then, the Grand Total is wrong, do you know how can I avoid it? thanks

the Power Bi Desktop file is in below URL.

https://1drv.ms/u/s!Am-wyNUhKsP7gx4yc41fsNF5Ne_E?e=GSePU9

Anonymous
7 years ago
Hi xuexi1890 ,

I think i got the solution at last

Grand Total = VAR _product = ALLSELECTED ( Table1[Product] ) VAR _Month = ALLSELECTED ( Table1[Resale Invoice Month] ) RETURN CALCULATE ( SUM ( Table1[Resale Qty] ), ALL ( Table1 ), Table1[Product] IN _product, Table1[Resale Invoice Month] IN _Month )

1.When no filter on Product or Month is selected it computes the total for the entire table.

2.When a Filter on Product alone is selected it computes the total for only that product for all months in the table.

3. When a filter on Month alone is selected it computes the total for only those Months for all products.

4. When a Product and Month is filtered, it computes the total for only those Products and those Months selected.

This should work.

Cheers

CheenuSing

11 Replies

Mariusz
Community Champion
7 years ago
Hi xuexi1890

Try this.
Grand Total = CALCULATE( SUM( Table1[Resale Qty] ), ALL( Table1 ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
- xuexi1890
  Helper I
  7 years ago
  Mariusz
  
  thank you Mariusz,
  
  if i try Grand Total = CALCULATE(sum(Table1[Resale Qty]),ALL(Table1)), then this number is grand total of the Table1,
  but i want it to be, the grand total of the product within whatever filter i have given.
  ie. the grand total has to be affected by slicers, but not respond to prices
  
  regards
  nate
  - Anonymous
    Not applicable
    7 years ago
    Hi xuexi1890 ,
    
    I was playing around with the attached pbix.
    
    1.The matrix visual is based on ReSale Price.
    
    2. The table visual is based on Product.
    
    So there is no commanility between the two. And hence when you use visual filter on product on the table visual you will not get same results.
    
    You also have a Page Level filter on Product, if you use the filtering on Product using this the result is the same in both visuals.
    
    The page level filter applies to all the visuals in a page.
    
    Hope this clarifies.
    
    Cheers
    
    CheenuSing