Replicating a Graph from Excel

[flemingg62]

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

[djurecic]

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/

[flemingg62]

Hi

Thanks, but I need a measure to count the numner of locations never used on each day 

[PaulDBrown]

Something along the lines of:

Never used =
VAR _Used =
    DISTINCTCOUNT ( Table[Locations] )
VAR _ALL =
    CALCULATE ( DISTINCTCOUNT ( Table[Locations), ALL(Table[Locations] ) )
RETURN
    _ALL - _Used

[flemingg62]

Hi - Thanks

Been paying around with it for a few hours and no luck, but almost there, the send part is returning the correct values, but the first part needs to be limited to desks never used in a day

Never used =

VAR _Used =

    DISTINCTCOUNT ( Table[Locations] ) I need to add a condition so that the values is limited to Location never used on a day, such as      FILTER(SUM(BI_SurveyData(Utilised] =0))      I have tried may variation but no luck

VAR _ALL =

    CALCULATE ( DISTINCTCOUNT ( Table[Locations), ALL(Table[Locations] ) )

RETURN

    _ALL - _Used

 I also have a measure, but no luck using this as well

Utilised Count = Sum((BI_SurveyData[Utilised]))

[PaulDBrown]

Can you share some dummy data or a link to a dummy PBIX file?

[flemingg62 · Accepted solution]

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