Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
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.
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |