Forum Discussion

ms5991's avatar
ms5991
Microsoft Employee
10 years ago

Line Chart Count/Sum/Stdv/etc Of...

I have two columns in a table, one called JobId, which is unique, and one called StartTime, which is a Date/Time cooresponding to when that job began and is not necessarily unique.  I would like to have a line graph where the x axis is the time, and the y axis is the value of each JobId, so I can see how JobId's are assigned over time.  The problem is that when I put StartTime in the axis box and JobId in the Values box, it only lets me do some sort of calculation on JobId, such as SUM, CountOf, etc.  I want the actual value, but that isn't an option.

 

I saw another post with this problem, and the given solution was to make sure the data column was of type WholeNumber, but JobId is of that type.  How can I do this? 

Need Help

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    ms5991

     

    In this secnario, you can use CALENDAR() function to generate a full calendar date table first. Create another table using GROUPBY() to group by your source table on StartTime column, aggregated with countrows of JobIds. The expression can be like:

     

    Table2= GROUPBY('table','table'[StartTime],"Count Of JobIds",CountRows(currentgroup()))

    Then you can LOOKUPVALUE() the [Count Of JobIds] column into calendar table based on date/time column.

     

     

    =LOOKUPVALUE('Table2'[Count Of JobIds], 'Table2'[StartTime], 'CalendarTable'[DateTime])

    Now you can create a line chart based on this Calendar table. Populate date time on X-Axis, and "Count Of JobIds" in Values.

     

     

    Regards,