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 ,

 

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

     

     

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

      Anonymous 

       

      i want my grand total all equal to 4,609,550, instead of 1,821,000 or 2,788,500

      if you play with the slicer here, when filter nothing on the product, the result is correct or you only select one month, instead of 2 months.

       

      https://1drv.ms/u/s!Am-wyNUhKsP7gx-eCJTTqjWCv93O

       

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