Forum Discussion

flemingg62's avatar
flemingg62
Helper I
3 years ago
Solved

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...
Need Help
  • flemingg62's avatar
    flemingg62
    3 years ago

    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
  • flemingg62's avatar
    flemingg62
    Helper I
    3 years ago

    Hi

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

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      3 years ago

      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's avatar
        flemingg62
        Helper I
        3 years ago

        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]))