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

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity 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.
Mariusz Repczynski

 

  • xuexi1890's avatar
    xuexi1890
    Icon for Helper I rankHelper 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'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)