Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 6
lbendlin
Super User
Super User

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.

Anonymous
Not applicable

I would like a measure to instead display text instead of values for those that are higher or lower than my expected value that's what I am trying to achieve. 

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

Anonymous
Not applicable

I managed to figure it out myself by grouping my data via power query then providing a measure to stipulate if the column was less than 1 to class it as missing data. Thank you for all your suggestions though.

lbendlin
Super User
Super User

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.

Anonymous
Not applicable

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Users online (1,462)