Forum Discussion
Calculate Expected Entries
Hi, I have a a dataset which constains a number of floors which are used to carry out heath and safety checks, these are categorised into buildings. I want to flag when an entry has not been completed on a date.
My expected values are 30 entries across 5 buildings, some buildings might have 6 floors therefore I need 6 daily entries, etc, across a month, as each month has a different number of days. I need a measure to calculate expected number of entries for buildings, then to filter across a month, which can be drilled down to highlight which exact date is missing an expected entry for that month.
Any help is much appreciated.
6 Replies
By default Power BI only shows data it has, not the data that is missing. To work around that enable "Show items with no data" and/or create dimension tables that list all buildings with all floors, regardless if they had a health check or not. You can then use the columns from these dimension tables in you visuals as the main columns.
Is there a measure what I can use a target that would state whether I have either a completed, missing entry or too many entries based on that I expect a specific amount of entries for each building on any given date. For instance, Building A has 6 floors, which I expect 1 entry per floor (whether full or partially complete) to be made every single date, if this isn't the case, then either a compete, partial or too many entries is given based on whether the 6 has been met or not. Below is a sample of the data that could be recorded.
Date Buildings Floors Extinguisher is in good condition Exit routes visible and clear Alarm drill working
04/01/2020 A 1 Yes No Yes
04/01/2020 A 2 Yes Yes Yes
04/01/2020 A 3 Yes No
04/01/2020 A 4 No Yes Yes
04/01/2020 A 5 Yes Yes Yes
04/01/2020 A 6 Yes Yes Yes
04/01/2020 B 1 Yes Yes No
04/01/2020 B 2 Yes Yes Yes
05/01/2020 A 1 Yes Yes Yes
05/01/2020 A 2 Yes Yes Yes
06/01/2020 A 3 Yes No Yes
07/01/2020 A 4 Yes Yes Yes
08/01/2020 A 5 Yes Yes No
09/01/2020 A 6 Yes Yes Yes
As I said above - create dimension tables that list all buildings with all floors, regardless if they had a health check or not.
You could theoretically glean that from the fact data (via VALUES() ) but that is risky since you may miss floors that _never_ had inspections.
Hi Anonymous ,
Would you please show us some sample data and your expected output for a better understanding? Maybe you need unpivot steps in your raw data.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
