Hi ! -- Average of a Measure (defined itself from measures on 2 different tables) including BLANKS

[DuvCam]

Hi experts people !

I have looked extensively on existing posts to see if there was already answers to this (simple) situation but no success …
Hope you can help me !  😊

I am working with PowerPivot Tables and DAX Measures (all in Excel)

The data:
COSTS being an explicit measure, defined by a DAX formula (it is not a column in a table), computed as the sum of 2 other Measures (from 2 different tables)
PERIOD is a column in the DataTable

COSTS	PERIOD
Product	2023_01	2023_02	2023_03	2023_04	Grand Total
GER	5,378	7,484	8,394	6,666	27,922
ROM		3,163	3,804	3,609	10,576
NOR	267	7,614	3,860	6,168	17,910
Grand Total	5,645	18,262	16,057	16,444	56,408

The wanted result:
Computation of for each Product of the AVERAGE of the GRAND TOTAL (let's call this wanted result "Average_GT")
In our case there are 4 periods with data => the average should be done for 4 periods => i.e. the GRAND TOTAL for each Product divided by number of period (with data for any of the Product)
=> what would be the DAX formula to define the Measure "Average_GT" ? Impossible so far to obtain this result …  ☹️

	Average_GT
Product
GER	6,980		Average computed on 4 months
ROM	2,644		Average computed on 4 months
NOR	4,477		Average computed on 4 months
Grand Total	14,102

Obtained so far:
Using the AVERAGEX formula allows to compute the Average, but it excludes the empty cells:

DynamicAverage = AVERAGEX(VALUES(Master_Calendar_Fiscal_Periods[PERIOD]), [COSTS])

	DynamicAverage	DynamicAverage#2
Product
GER	6,980	6,980		Average computed on 4 months: OK
ROM	3,525	3,525		Average computed on 3 months: Not OK !!
NOR	4,477	4,477		Average computed on 4 months: OK
Grand Total	14,102	14,102

I get the same result if trying:

DynamicAverage#2 = AVERAGEX( CALCULATETABLE(  VALUES (Master_Calendar_Fiscal_Periods[PERIOD] ) , ALL (Data_Table[Product])) , [COSTS])

Many thanks in advance for any help ! Any question let me know!

Camille

[v-zhangti]

Hi, @DuvCam 

You can try the following methods.

Measure = 
Var _Sum=CALCULATE(SUM('Table'[COSTS]),ALLEXCEPT('Table','Table'[Product]))
Var _Count=CALCULATE(DISTINCTCOUNT('Table'[PERIOD]),ALL('Table'))
Return
DIVIDE(_Sum,_Count)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DuvCam]

Hi @v-zhangti ,
Thank you for your feedback.
Proposed solution tested and not working unfortunately.
1/ For _Sum: my COSTS is a measure, not a cloumn, therefore proposed function SUM not working => I get around this with SUMX function. We should be OK here.
2/ For _Count: challenge is that my measure COSTS is defined from 2 different measures belonging to 2 different tables "Table" (the same as where COSTS is defined) and "Table2".
I am modifying the definition of _Count to:

Var _Count=CALCULATE(DISTINCTCOUNT('Table'[PERIOD]),ALL('Table')) + CALCULATE(DISTINCTCOUNT('Table'[PERIOD]),ALL('Table2'))

 The returned result is unfortunately a 3 for the Product "ROM", instead of the 4 that would allow to compute the expected average.

Product	_Count (obtained)	_Count (expected)
GER	4	4
NOR	4	4
ROM	3	4
TOTAL	4	4

Let me know if I have mis-understood / mis-tried one of the steps, otherwise most grateful for any other suggestions ! 😊

[v-zhangti]

Hi, @DuvCam 

You can try the following methods.

Count = 
Var _table=DISTINCT(UNION(VALUES('Table'[PERIOD]),VALUES('Table 2'[PERIOD])))
Var _count=COUNTROWS(_table)
Return
_count

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DuvCam]

Hi @v-zhangti ,
Thanks for the further feedback.
Trying the new suggestion, I unfortunately still obtain an incorrect value of 3 for product ROM.

Product	_Count (obtained)
GER	4
NOR	4
ROM	3
TOTAL	4

Any other suggestion ?