Forum Discussion
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 total per dept but also the total for all depts. That would be a sum of 21. Per the documentation the all function must be used on a base table/base column. That gives me a total of 51 which is the value of the entire table not the filtered table. What other function is available to sum a filtered column? Thank you
TABLE:
Day;Dept;Units
1;A;4
1;B;3
1;C;8
2;A;5
2;B;7
2;C;3
3;A;1
3;B;0
3;C;2
4;A;6
4;B;7
4;C;5
DESIRED RESULT
Dept;Dept_Total;Total
A;7;21
B;7;21
C;7;21
Hi,
Try these measures
Measure1 = sum(Data[Units])
Measure2 = calculate([Measure1],allselected(Data[Dept]))
Hope this helps.
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.
6 Replies
- Ashish_Mathur
Super User
Hi,
Try these measures
Measure1 = sum(Data[Units])
Measure2 = calculate([Measure1],allselected(Data[Dept]))
Hope this helps.
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.
If I'm understanding what you're trying to do correctly, that last column should happen automatically with a standard measure. So create a new Measure on that table (right-click the table, new measure).
TotalUnits = sum([Units])
Add a Table to the report with the Dept, DeptTotal, TotalUnits fields.
Oh wait - just noticed the consolidation in the middle column... Give me a sec...
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.
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 🙂
