Dax, Average + IsFiltered

[Anonymous]

Hello, all.

date	category	name unit	countUnit	tradePoint	Zone
01.01.2019	Bike	Bike1	356	1A	A
02.01.2019	Bike	Bike2	388	6C	C
03.01.2019	Bike	Bike3	176	9C	C
04.01.2019	Bike	Bike4	391	9C	C
05.01.2019	Bike	Bike5	443	3C	C
06.01.2019	Car	Car6	500	4A	A
07.01.2019	Car	Car7	452	9C	C
08.01.2019	Car	Car8	306	3C	C
09.01.2019	Car	Car9	295	7A	A
10.01.2019	Robots	Robots10	490	2B	B
11.01.2019	Robots	Robots11	466	9C	C
12.01.2019	Robots	Robots12	261	4A	A
13.01.2019	Robots	Robots13	488	2B	B
14.01.2019	Bike	Bike1	199	9C	C
15.01.2019	Bike	Bike2	397	6C	C
16.01.2019	Bike	Bike3	291	1A	A
17.01.2019	Bike	Bike4	496	6C	C
18.01.2019	Bike	Bike5	348	1A	A
19.01.2019	Car	Car6	247	7A	A
20.01.2019	Car	Car7	411	7A	A
21.01.2019	Car	Car8	378	8B	B
22.01.2019	Car	Car9	426	2B	B
23.01.2019	Robots	Robots10	296	8B	B
24.01.2019	Robots	Robots11	268	10A	A
25.01.2019	Robots	Robots12	461	5B	B
26.01.2019	Robots	Robots13	209	7A	A
01.01.2019	Bike	Bike1	275	7A	A
02.01.2019	Bike	Bike2	329	6C	C
03.01.2019	Bike	Bike3	173	10A	A
06.01.2019	Car	Car6	377	9C	C
07.01.2019	Car	Car7	399	10A	A
08.01.2019	Car	Car8	475	10A	A
09.01.2019	Car	Car9	271	6C	C
10.01.2019	Robots	Robots10	396	5B	B
11.01.2019	Robots	Robots11	431	5B	B
12.01.2019	Robots	Robots12	281	7A	A
14.01.2019	Bike	Bike1	491	3C	C
15.01.2019	Bike	Bike2	163	6C	C
16.01.2019	Bike	Bike3	250	10A	A
17.01.2019	Bike	Bike4	245	5B	B
18.01.2019	Bike	Bike5	294	3C	C
19.01.2019	Car	Car6	430	6C	C
21.01.2019	Car	Car8	304	1A	A
22.01.2019	Car	Car9	323	3C	C
23.01.2019	Robots	Robots10	285	8B	B
24.01.2019	Robots	Robots11	479	7A	A
25.01.2019	Robots	Robots12	404	7A	A

I have an approximate such table with the values of products and categories that were sold in points. Points are part of a zone.

I calculate the average value of sales for the period by days. If you look at the point level, the calculation is correct.

But how do I calculate the average in the zone for these points?

  

Using the example of zone B, we have 3 points with average values of 468, 383, 480. The average for the zone will be 444 , but measure calculate = sumCount (6226) / distinctDate(15) = 415 is wrong

How do I calculate the average points for a zone , use function ISFILTERED () ?

And if it is possible calculate avarage for the total (maybe a function of Hasonevalue() )?

[v-danhe-msft]

Hi @Anonymous,

How could you get the AverageCount in your Matrix table? If I used the "Averahe" option, I get the different data from you:

Could you please offer me more information about it?

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft ,

It is important that the mean value of the zone is calculated from the mean values of the points.

I have created a  file that displays the calculations, please see .

The average value is calculated differently, for points it is sales divided by the number of dates, for a zone it is the average of the values of the average points.

And I can not use the Avarage function on a column, because the points are filtered by attribute in the "sum" measure itself.

[Anonymous · Accepted solution]

This problem is solved by :

AvSum = IF(  ISFILTERED(CounterParties_structure[TradePoint]) ;

DIVIDE([SUM_count];[DistCount_date];BLANK()) ;

IF( not(ISFILTERED(CounterParties_structure[TradePoint])) ;

SUMX(VALUES(Data_query[TradePoint]); [SUM_count]/CALCULATE([DistCount_date]))/DISTINCTCOUNT(Data_query[TradePoint])))