Comparing averages to a baseline date

[Anonymous]

Hello everyone!

I want to take some averages that come from a baseline date, then take averages from all other dates and subtract the second one from the first. I have created a formula that is shown bellow but it doesn't seem to work. I have it on a matrix visualization where each column is a date.

AET Difference with Baseline =

VAR baselineAverage = CALCULATE([Average Event Time], FILTER(ALL('Runtime Info'), [Date] = DATE(2020, 04, 03)))

VAR currentAverage = CALCULATE([Average Event Time], FILTER(ALL('Runtime Info'), [Date]))

RETURN

baselineAverage - currentAverage

Any help would be appreciated. Thank you!

[az38]

Hi @Anonymous 

it depends on your data model, but from a first sight you dont need any filter in current calculation

AET Difference with Baseline =
VAR baselineAverage = CALCULATE([Average Event Time], FILTER(ALL('Runtime Info'), [Date] = DATE(2020, 04, 03)))
VAR currentAverage = CALCULATE(AVERAGE([Average Event Time]))

RETURN
baselineAverage - currentAverage

does [Average Event Time] column or measure?

[Anonymous]

@az38  So I replaced the formula with the one you sent me and I still didn't get the correct results. The 3rd of April is the base line so I suppose the AET Difference with Baseline should me 0. Bellow are the results I get. Any thoughts?

[az38]

@Anonymous 

what the statement do you use for [Average Event Time]  measure?

[Anonymous]

@az38  I use this one

Average Event Time = AVERAGE('Runtime Info'[Event Time])

[az38]

@Anonymous 

ypur statement looks good enough

didn't your try to debug? whats component is wrong? baselineAverage  or currentAverage?

also, try this

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

RETURN
baselineAverage - currentAverage

[Anonymous]

@az38 

it seems that baselineAverage is the one that's off. using the bellow measure I get the following result.

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

RETURN

baselineAverage

[az38]

@Anonymous 

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?

[Anonymous]

@az38 

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

[Anonymous]

@az38 

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?

[Anonymous]

@az38 

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

RETURN

baselineAverage

do you have 7.71 if you use

RETURN
baselineAverage - currentAverage

?

[Anonymous]

@az38 

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

[Anonymous]

@az38 

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

[Anonymous]

@az38 

Unfortunately it contains sensitive data so I can't share it with you. But I really appreciate the gesture!

[Anonymous]

Hi @az38 

It's a measure!