Average of a value

[Heinrich]

Hello

I have a Line Chart with 

X-Axis = Time (Direct Query)

Y-Axis = Value (Direct Query)

I would like to display the Y-Axis as Average of a day instead of hours as this is now on the X-Axis.

Do you have a possibility

Regards
Heinrich

[v-sdhruv]

Hi @Heinrich ,
Yes. This would be more convinient.
I hope you were able to resolve your issue.
If If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thanks!
Shruti

[v-sdhruv]

Hi @Heinrich ,
Yes, relationship wont be established with Primary key if it has null values.
In this case, try this measure instead:
DailyAverageMeasure =
AVERAGEX (
SUMMARIZE (
'YourTable',
FORMAT('YourTable'[Timestamp], "yyyy-mm-dd"),
"DailyAvg", AVERAGE('YourTable'[Value])
),
[DailyAvg]
)
On X- axis- use calculated column as-
DateOnly = FORMAT('YourTable'[Timestamp], "yyyy-mm-dd")
Then on Y-axis use 
DailyAverage Measure 
Hope this helps!
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[Heinrich]

Hello @v-sdhruv 
Hope you had a great weekend

I have tried this measure but got an error

To summarize for your solution I need only
1. Measure on the Direct Query Table
2. A calculated column on the Direc Query Table 

And then put them both together 1 on Y-Axis and 2 on X-Axis
Regards
Heinrich

[v-sdhruv]

Hi @Heinrich 

Yes, Summarize  is a function which returns a Table.It cannot be used in Calculated Columns and Measures Directly.

However you can use this code as a CALCULATED TABLE.
Go to modelling--> New Table and use this measure.
This will help you with the result.
Hope this helps!

[Heinrich]

Hello @v-sdhruv 
I am very sorry but it does not work.
1. The "Daily Average Measure..." code is a measure and done in the preexisting Direct Query Table. Right? If yes then I get an error shown above.

2. If this has to be inserted in a new calculated table with the code above then I dont get the columns of the preexisting Direct Query Table to choose.

Or do you mean

1. To do a measure in Direct Query Table

    What would be the Measure 

2. Do a calculated table

As a workaround could it be done like that

1. Copy the values of time and NER to a new table

2. Remove all empty fields from both columns

3. Do a daily average of time and NER

Regards
Heinrich

[Heinrich]

Hello 
I managed to copy following columns to a new table (imported mode)

1. NER (Value)

2. FQDN (Text)

3. Start Time (Date & Time)

Now do another table to get daily average. Would that be a solution.
Regards
Heinrich

[v-sdhruv · Accepted solution]

Hi @Heinrich ,
Thats great!
You can simply use this measure to calculate average-
DailyAverage =
AVERAGEX(
VALUES('ImportedTable'[Start Time]),
CALCULATE(AVERAGE('ImportedTable'[NER]))
)
You can change the data-type of Start Time as date and use this in your Y-axis.

If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[Heinrich]

Ok @v-sdhruv 
Great so instead of doing 1+2 just do a single measure but on the same facttable (which is in direct query) right?

[v-sdhruv]

Hi @Heinrich ,
You can create a seperate table as-
1. DateTable = CALENDAR(MIN(YourTable[Timestamp]), MAX(YourTable[Timestamp]))
2.Create a relationship between Your table and Date table and make sure it’s Single-direction and Many-to-One (from fact table to Date table).
3.Crete you Daily avg measure-
DailyAverageMeasure =
CALCULATE(
AVERAGE('YourTable'[Value]),
USERELATIONSHIP('DateTable'[Date], 'YourTable'[Timestamp])
)
Then use this in your visual.
or you can try with summarize function to create a new Table as-
DailyAverageMeasure =
AVERAGEX(
SUMMARIZE(
'YourTable',
DATE(YEAR('YourTable'[Timestamp]), MONTH('YourTable'[Timestamp]), DAY('YourTable'[Timestamp])),
"DayAvg", AVERAGE('YourTable'[Value])
),
[DayAvg]
)
And use the fields in your visuals.
Hope this helps!
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[Heinrich]

Hi @v-sdhruv 
Thank you very much for your extensive explanation
1. I get an error when creating the table 
    OLE DB or ODBC error: [Expression.Error] We cannot convert the specified value to the specified type..

2. The relationship can not created with "Many-to-One" When I do that, than it states: 

-> This could replace empty cells
= Table.ReplaceValue(
#"Changed Type","null","blank",Replacer.ReplaceValue,{"Column1","text"}
)

Which might resolve 1+2 but how should that be inserted

3. Where should the Measure be placed on the newly created Table?
Have a great day
Heinrich

[Heinrich]

Hello @Akash_Varuna 
I get this error assigning the measure to the Y-axis 

and how do I group the time in day.
Regards
Heinrich

[Akash_Varuna]

@Heinrich Yes, you can create the measure in another table. Measures are not tied to specific tables in Power BI—they can reference any table in your model. It will work as long as the measure references the original table's fields.

[Heinrich]

Hello @Akash_Varuna 

Thank you very much for your fast answer.

A can not add the calculated column in the original table as it is administered by others.

Can I do that on a another table?
Regards
Heinrich

[Akash_Varuna]

@Heinrich For this, create a calculated column or measure like:

DailyAverage = AVERAGEX(VALUES(YourTable[Date]), AVERAGE(YourTable[Value]))  

Use this measure on the Y-axis and group the X-axis by day instead of hours.