Forum Discussion

xuexi1890's avatar
xuexi1890
Icon for Helper I rankHelper 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,...
Need Help
  • Anonymous's avatar
    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

Anonymous's avatar
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

 

 

xuexi1890's avatar
xuexi1890
Icon for Helper I rankHelper I
7 years ago

Anonymous 

thanks CheenuSing.

 

perhaps, i didn't fully understand your point. but for me, i want my grand total respond to the slicers, while keeping the grand total per product.

 

let me rephrase my request in a simpler way. how can i get the grand total equal to the other 2 grand totals shown below. ( i have slicers on product, resale invoice month and maybe more on sales area)

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi  xuexi1890 ,

     

    Please try

     

    Grand Total =
    Var _product = SELECTEDVALUE(Table1[Product])
    Return
    CALCULATE(sum(Table1[Resale Qty]),ALL(Table1),Table1[Product] = _product)
     
     
    This will then display the grand total for the Product[ID] value. 
     
    Let me know if this works.
     
    Cheers
     
    CheenuSing
    • xuexi1890's avatar
      xuexi1890
      Icon for Helper I rankHelper I
      7 years ago

      Anonymous 

      thanks, but it is not responding to my slicers. it is a grand total ignoring my slicers.

      how can i tell DAX that my dataset of the ALL() is narrowed down because of the slicer.

       

      Cheers

      Nate

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi xuexi1890 ,

         

         I am still not clear of your requirement.

         

        Can you please put in excel file the output you expect for the sample data  when

         

        1.  No slicer for Product ID is selected

        2. When a slicer for Product ID is selected

         

        From where you select the product , is it from the table visual or from the Visual Filter in the Visualisation Pane for the table visual.

         

         

        Cheers

         

        CheenuSing