Forum Discussion
Replicating a Graph from Excel
Hi
I need help reproducing a graph I have in Excel in Powe BI and have been trying to write a measure to use in graphs
It calculates how many desk were utilised (at least once in the day) or never utilised on a giving day. Where the data is recorded every hour (Slots)
I have 3 Tables that are linked
BI_Location_Building_Data (A list of all location (Desks))
- Location
- Building
- Floor
- Department
BI_Slots
- Date Calculated from Time Start 14/03/2001
- TimeStart(Slots) Sample 14/03/2001 12:00
- 14/03/2001 13:00
- 14/03/2001 14:00
- 14/03/2001 15:00
- 14/03/2001 16:00
BI_SurveyData
- ID Autonumber
- Location
- Utilised 1 or 0
Can anyone help
Hi
Thanks cracked it hsed need to add a filter
Locations Never Never used =VAR _Used =CALCULATE(DISTINCTCOUNT(BI_SurveyData[Location]), FILTER(BI_SurveyData,BI_SurveyData[Utilised]=1))VAR _ALL =CALCULATE ( DISTINCTCOUNT ( BI_SurveyData[Location]), ALL(BI_SurveyData[Location]) )RETURN_ALL - _Used
6 Replies
Hi flemingg62 ,
Do you have a specific question that we can answer? You should be able to achieve this using a 100% Stacked Bar Chart.
https://powerbidocs.com/2020/01/25/power-bi-100-stacked-bar-chart/
Hi
Thanks, but I need a measure to count the numner of locations never used on each day
Something along the lines of:
Never used = VAR _Used = DISTINCTCOUNT ( Table[Locations] ) VAR _ALL = CALCULATE ( DISTINCTCOUNT ( Table[Locations), ALL(Table[Locations] ) ) RETURN _ALL - _Used
