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

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      3 years ago

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

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