Forum Discussion
Calculate ALL Help
Hi. I have a some data has the following data: Contract, Date ,Employee, Hours, Task. Here is a table w/ hours summarized at the contract level.
| Contract | Hours |
| ABC | 1000 |
| CDE | 2000 |
| EFG | 3000 |
I want to be able to filter by date/contract but ignore task. I figure the calculation is Contract Hours = Calculate(SUM[Hours]),ALL(Task))
Here is my expectation.
| Contract | Task | Contract Hours | Hours |
| ABC | 1 | 1000 | 250 |
| ABC | 2 | 1000 | 250 |
| ABC | 3 | 1000 | 250 |
| ABC | 4 | 1000 | 250 |
| CDE | 1 | 2000 | 500 |
| CDE | 2 | 2000 | 500 |
| CDE | 3 | 2000 | 500 |
| CDE | 4 | 2000 | 500 |
| EFG | 1 | 3000 | 600 |
| EFG | 2 | 3000 | 600 |
| EFG | 3 | 3000 | 600 |
| EFG | 4 | 3000 | 600 |
| EFG | 5 | 3000 | 600 |
However, the results are very different. I figure this should be a pretty easy but I'm definitely missing something.
2 Replies
Using ALLEXCEPT(table, table[contract]) as the filter expression will solve this. However, as always, it depends on how your model is set up and which fields you are using (Dimension tables, date table...) in your target visual.
Hi dyee4613 , I tested your measure and it works well to display your expectation in the table visual. I also tested another measure using ALLEXCEPT function which also works as below. Can you check or confirm is there any other fields used in the visual or any filters applied which may affect the context for the measure?
Contract Hours = Calculate(SUM('Table (2)'[Hours]),ALL('Table (2)'[Task])) Contract Hours 2 = Calculate(SUM('Table (2)'[Hours]),ALLEXCEPT('Table (2)','Table (2)'[Contract]))Here is the sample data I used:
And the table visual result:
Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
