Newbie to Power BI with challenging data problem

[DodSmith]

I am new to Power BI. I understand the principles and have been playing with the data. Looks mostly straightforward  to use. I however have a challenge I hope that someone can help me. with
 
I have a dataset of logged data. The data consists of a set of variable names (VarName) with a Variable value(VarValue) and a timestamp (tiimestring) of when the value was captured. See a selection of the data below.
 
I want to take an average of the values for each variable at every timestamp (more than one value for one timestamp). Then I want to produce a line graph with lines for each variable with the timestamping as the x axis and value as the y axis.
 
This would be a basis for a data logger function I want to implement later where Ideally I want to go si to build a vertical logger with window with the variable names at the top and timestamping on the left and the values along the bottom. But this will come later.
I would appreciate any help on this.
 
Best regards GGS

VarName	TimeString	VarValue
API_600rpm	19.07.2019 08:23	73.56052
API_300rpm	19.07.2019 08:23	43.72612
API_200rpm	19.07.2019 08:23	33.54956
API_100rpm	19.07.2019 08:23	23.51505
API_6rpm	19.07.2019 08:23	7.696274
API_3rpm	19.07.2019 08:23	7.448882
API_600rpm	19.07.2019 08:23	73.41819
API_300rpm	19.07.2019 08:23	43.62548
API_200rpm	19.07.2019 08:23	33.47369
API_100rpm	19.07.2019 08:23	23.46848
API_6rpm	19.07.2019 08:23	7.691678
API_3rpm	19.07.2019 08:23	7.443955
API_600rpm	19.07.2019 08:23	73.29433
API_300rpm	19.07.2019 08:23	43.55198
API_200rpm	19.07.2019 08:23	33.42208
API_100rpm	19.07.2019 08:23	23.43895
API_6rpm	19.07.2019 08:23	7.68972
API_3rpm	19.07.2019 08:23	7.441484
API_600rpm	19.07.2019 08:24	73.2063
API_300rpm	19.07.2019 08:24	43.16627
API_200rpm	19.07.2019 08:24	33.05322
API_100rpm	19.07.2019 08:24	23.15962
API_6rpm	19.07.2019 08:24	7.679123
API_3rpm	19.07.2019 08:24	7.427405
API_600rpm	19.07.2019 08:24	73.26457
API_300rpm	19.07.2019 08:24	43.14558
API_200rpm	19.07.2019 08:24	33.02204
API_100rpm	19.07.2019 08:24	23.1307
API_6rpm	19.07.2019 08:24	7.67869
API_3rpm	19.07.2019 08:24	7.427323
API_600rpm	19.07.2019 08:24	73.32072
API_300rpm	19.07.2019 08:24	43.13333
API_200rpm	19.07.2019 08:24	32.99954
API_100rpm	19.07.2019 08:24	23.1089
API_6rpm	19.07.2019 08:24	7.678564
API_3rpm	19.07.2019 08:24	7.427793
API_600rpm	19.07.2019 08:24	73.36995
API_300rpm	19.07.2019 08:24	43.12649
API_200rpm	19.07.2019 08:24	32.98364
API_100rpm	19.07.2019 08:24	23.09279
API_6rpm	19.07.2019 08:24	7.678606
API_3rpm	19.07.2019 08:24	7.42849
API_600rpm	19.07.2019 08:24	72.32797
API_300rpm	19.07.2019 08:24	42.88657
API_200rpm	19.07.2019 08:24	32.9267
API_100rpm	19.07.2019 08:24	23.17574
API_6rpm	19.07.2019 08:24	7.697506
API_3rpm	19.07.2019 08:24	7.461083
API_600rpm	19.07.2019 08:25	72.3369
API_300rpm	19.07.2019 08:25	42.90461
API_200rpm	19.07.2019 08:25	32.94062
API_100rpm	19.07.2019 08:25	23.18999
API_6rpm	19.07.2019 08:25	7.701194
API_3rpm	19.07.2019 08:25	7.469333
API_600rpm	19.07.2019 08:25	72.3615
API_300rpm	19.07.2019 08:25	42.92539
API_200rpm	19.07.2019 08:25	32.95452
API_100rpm	19.07.2019 08:25	23.20271
API_6rpm	19.07.2019 08:25	7.704765
API_3rpm	19.07.2019 08:25	7.477392
API_600rpm	19.07.2019 08:25	72.40051
API_300rpm	19.07.2019 08:25	42.9482
API_200rpm	19.07.2019 08:25	32.96788
API_100rpm	19.07.2019 08:25	23.21349
API_6rpm	19.07.2019 08:25	7.708211
API_3rpm	19.07.2019 08:25	7.485224
API_600rpm	19.07.2019 08:25	72.45141
API_300rpm	19.07.2019 08:25	42.973
API_200rpm	19.07.2019 08:25	32.98118
API_100rpm	19.07.2019 08:25	23.22311
API_6rpm	19.07.2019 08:25	7.711555
API_3rpm	19.07.2019 08:25	7.49289

[edhans]

See if this is what you need @DodSmith 

Average Value =
VAR CurrentVariable =
    MAX( 'Table'[VarName] )
VAR CurrentTimeStamp =
    MAX( 'Table'[TimeString] )
VAR AverageValue =
    AVERAGEX(
        FILTER(
            ALL( 'Table' ),
            'Table'[VarName] = CurrentVariable
                && 'Table'[TimeString] = CurrentTimeStamp
        ),
        'Table'[VarValue]
    )
RETURN
    AverageValue

It returns this:

My PBIX file is here if you want to see it.

[harshnathani]

Hi @DodSmith ,

Use the measure =

Measure = CALCULATE (AVERAGE ('Table'[VarValue]), ALLEXCEPT('Table', 'Table'[VarName], 'Table'[TimeString])

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[DodSmith]

Hi, where do you applyt the formula that you made and how?

Best Regards

[edhans]

@DodSmith it is a measure. See the PBIX file I linked to. A measure just goes wherever. Right-click on a table and select New Measure, but measures don't care what table they are in. I create "measure tables" to put them in and keep them out of my actual tables. 

Then just drop the measure into the Values section of the chart.

You don't want to generally create a new column, called a calculated column, in Power BI. That is something Excel users (like myself) immediately gravitate towards. But calcualted columsn are not efficient and should be avoided for most things.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

[harshnathani]

Hi @DodSmith ,

You will need to create a new measure.

Right Click the table in the Field's Section, you will see an option 'New Meaure'. This is also avaiable in the Ribbon above.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[DodSmith]

Hi again,

OK I had figured that out but when I put the DAX formula in place, I get an error that says cannot find table *Table'. Do I have to name the table of data I have first to be 'TAble'?

[edhans · Accepted solution]

On the table name, no. My sample data was just called Table. If yours is called Sales, then your measure would be Sales[Field Names], or 'Inventory Values'[Some Filed Name]

Table names generally are in single quotes, and field names are always in square brackets.