Average Measure with Blanks cells

[joshua1990]

Hello everyone!

I have a huge transactional table that shows me the Actuals vs. Target for machines, per row.
The structure is as follows:

DateTime	DateEnd	Machine	Actual	Target
01.01.2021 00:05:00	01.01.2021 01:05:00	1	5	4
01.01.2021 01:05:00	01.01.2021 02:05:00	1	5	4
01.01.2021 02:05:00	01.01.2021 05:05:00	1
01.01.2021 05:05:00	01.01.2021 06:00:00	1	4

As you can see, there are three cases to distinguish here:

• Case 1: All cells are felt.
  2nd case: No Actuals or Targets
  3rd case: Only actuals.

Now I want to calculate the average for the column "Targets".
The second case - no actuals/targets - should not be included.
In the third case - only actuals - the target should be equal to the actuals.

Unfortunately, the following approach does not give me a correct value for targets:

Target  = 
VAR _Check = IF(ISBLANK(AVERAGE(data[target])),[Actual],AVERAGE(data[target]))
RETURN
_Check

[AlB]

Hi @joshua1990 

Try this.

Target =
CALCULATE (
    AVERAGEX ( data, IF ( ISBLANK ( data[target] ), data[actual], data[target] ) ),
    data[target] <> BLANK (),
    data[actual] <> BLANK ()
)

If it is too slow we'll try another approach

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.