Forum Discussion

Arturo24's avatar
Arturo24
Frequent Visitor
2 years ago
Solved

Sum a column from a filtered table

How do I sum a column form a filtered table that returns the entire value of that filtered column?   Example:  If I filter the table below for the laSt two days: days 3 and 4, I want to return the ...
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Try these measures

    Measure1 = sum(Data[Units])

    Measure2 = calculate([Measure1],allselected(Data[Dept]))

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Arturo24 ,

    First of all, many thanks to Ashish_Mathur  for your very quick and effective replies.

    Based on my testing, please try the following methods:

    1.Create the simple table.

    2.Create the new measure to filter the day and return every dept total.

     

    Dept total = CALCULATE(SUM('Table'[Units]), FILTER('Table', 'Table'[Day] in {3,4}))

     

     

    3.Create the new measure to calculate all dept total.

     

    Total = CALCULATE([Dept total], ALL('Table'[Dept]))

     

    4.Drag the measures into the table visual.

    5.The result is shown below.

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

LightSQR's avatar
LightSQR
Frequent Visitor
2 years ago

What about accomplishing this via a measure (named “DeptTotal”) and a Matrix with a slicer. 

 

I created a table with your data, then created the measure:

DeptTotal = sum('Table'[Units])

 

Then added a slicer with the “Day” field, then came up with this after filtering with the slicer:

 

(I also put a table with the fields as well just so I could see the data).

 

But by choosing only Day 3 & 4, I get the subtotal for that Dept, but at the bottom get the full filtered total of 21. 

Arturo24's avatar
Arturo24
Frequent Visitor
2 years ago

I know I can do it with a slicer.  Sorry should have said that earlier.  Trying to avoid a slicer or a calcualted column.  Should have also said that. 

 

If I do filter the table, what function/method gives me the overall total of 21?   

 

And thank you for taking the time to answer my question.  Much appreciated 🙂