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,
Im trying to count a subset of my data using Calculate and sum and applying some filters.
i'm applying the same filters in the data view to check the validity of the query however the numbers are slightly out .
Here is the my query.
Hi Josh,
Query looks fine. The difference might have something to do with counting blanks.
If that's not the case, could you upload an example of the dataset? It's hard to answer your question without any extra information of the dataset.
@Anonymous
One way to test the differenct woulb be to apply the filters one at a time and see which one causes the amount to be off. I modified your original a bit to use IN and lists rather than <> for each TradeName for example:
Overdue Count =
CALCULATE (
SUM('Current WIP Run'[Job Count] )
,NOT ( 'Current WIP Run'[CurrentStage] IN {"No Access - Job to be Abandoned", "Card Left & 1st Visit"} )
,NOT ( 'Current WIP Run'[Resource (groups)] IN { "SUB" } )
,NOT ( 'Current WIP Run'[priorityType] IN { "Call Back" } )
,NOT ( 'Current WIP Run'[TradeName] IN {"Building Services Inspections", "Electrical Programme", "Fire Assessment Remedial Work", "Asbestos Removal", "Tech Inspection", "Electrical AFD Work", "WORK SUB CONTRACTED", "Rubbish Clearance", "Asbestos Removals HS", "Planned Schemes", "Electrical Communal Testing", "Electrical Communal Testing", "Labourer"} )
)
You can turn off the filters one at a time by putting '--' (double minus sign) ahead of the line so if I want to turn of all but the filter on [TradeName] it looks like this.
Overdue Count =
CALCULATE (
SUM('Current WIP Run'[Job Count] )
--,NOT ( 'Current WIP Run'[CurrentStage] IN {"No Access - Job to be Abandoned", "Card Left & 1st Visit"} )
--,NOT ( 'Current WIP Run'[Resource (groups)] IN { "SUB" } )
--,NOT ( 'Current WIP Run'[priorityType] IN { "Call Back" } )
,NOT ( 'Current WIP Run'[TradeName] IN {"Building Services Inspections", "Electrical Programme", "Fire Assessment Remedial Work", "Asbestos Removal", "Tech Inspection", "Electrical AFD Work", "WORK SUB CONTRACTED", "Rubbish Clearance", "Asbestos Removals HS", "Planned Schemes", "Electrical Communal Testing", "Electrical Communal Testing", "Labourer"} )
)
Give that a try, see if you can track down what is causing your variance. Maybe one of the fields has a space on " Labourer" on some rows or something.
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |