Forum Discussion
Comparing averages to a baseline date
Community ChampionAnonymous
no, baseline is good. it's the same in each rows as expected.
for me this statement also work ok.
please, show your data model more detailed. what columns do you use?
Yes that is my fault, I should have shared the model.
For this calculation I only use Runtime Info table. The model is something like this:
I have multiple queries in the Query column where each query has 20 event IDs. Each event ID has one response time (event time) and the event count (which is the date returned).
In one day I run each query more than once so I want for each query to get an average response time (event time) from all of its event IDs.
I hope it makes more sense now.
- az38
Anonymous
If i understand you correct you need an average Event Time by Query?
if so, try like
AET Difference with Baseline = VAR baselineAverage = CALCULATE(AVERAGEX(FILTER(ALL('Runtime Info'), [Date] = DATE(2020, 04, 03)), [Event Time]) VAR currentAverage = CALCULATE(AVERAGE([Event Time]), ALLEXCEPT('Runtime Info', 'Runtime Info'[Query])) RETURN baselineAverage - currentAverage Yes I want the average of each query. It still didn't work though. I still get the same value on each query. By the way
VAR currentAverage = CALCULATE(AVERAGE([Event Time]), ALLEXCEPT('Runtime Info', 'Runtime Info'[Query]))produces an error in the bold spot. Did you mean the one below?VAR currentAverage = CALCULATE(AVERAGE('Runtime Info'[Event Time]), ALLEXCEPT('Runtime Info', 'Runtime Info'[Query]))- az38
Anonymous
yes, your statement is better.
current average is the same for each rows or calculated incorrect?
This is what I get. Seeing the total row my guess is that somewhere it the total average of all queries while I want the average of each individual Query.
- az38
Anonymous
you've got 7.71 when you tried
RETURNbaselineAveragedo you have 7.71 if you useRETURN baselineAverage - currentAverage?
Sorry my bad I forgot to put the current date. This is what I get, which I think is still wrong. If the 3rd of April is the baseline then shouldnt the measure produce 0 on each Query for that date?
- az38
Anonymous
no. it means you need average by Query and date
so, it shuld look like
AET Difference with Baseline = VAR baselineAverage = CALCULATE(AVERAGEX(FILTER(ALL('Runtime Info'), [Date] = DATE(2020, 04, 03)), [Event Time]) VAR currentAverage = CALCULATE(AVERAGE('Runtime Info'[Event Time]), ALLEXCEPT('Runtime Info', 'Runtime Info'[Query], 'Runtime Info'[Date])) RETURN baselineAverage - currentAverage Unfortunately nothing changed. Still not the correct result. Anyway I believe it is frustrating enough 😛 I shall try out some other stuff to see if they work and if not I'll see what I can do. If you have any other ideas please don't hesitate to port them, or if not thank you anyway for your help and patience!
- az38
Anonymous
you can share you pbix-file and I will have a look at
Unfortunately it contains sensitive data so I can't share it with you. But I really appreciate the gesture!