cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nick_kz Regular Visitor
Regular Visitor

Dax, Average + IsFiltered

Hello, all.

 

datecategoryname unitcountUnittradePointZone
01.01.2019BikeBike13561AA
02.01.2019BikeBike23886CC
03.01.2019BikeBike31769CC
04.01.2019BikeBike43919CC
05.01.2019BikeBike54433CC
06.01.2019CarCar65004AA
07.01.2019CarCar74529CC
08.01.2019CarCar83063CC
09.01.2019CarCar92957AA
10.01.2019RobotsRobots104902BB
11.01.2019RobotsRobots114669CC
12.01.2019RobotsRobots122614AA
13.01.2019RobotsRobots134882BB
14.01.2019BikeBike11999CC
15.01.2019BikeBike23976CC
16.01.2019BikeBike32911AA
17.01.2019BikeBike44966CC
18.01.2019BikeBike53481AA
19.01.2019CarCar62477AA
20.01.2019CarCar74117AA
21.01.2019CarCar83788BB
22.01.2019CarCar94262BB
23.01.2019RobotsRobots102968BB
24.01.2019RobotsRobots1126810AA
25.01.2019RobotsRobots124615BB
26.01.2019RobotsRobots132097AA
01.01.2019BikeBike12757AA
02.01.2019BikeBike23296CC
03.01.2019BikeBike317310AA
06.01.2019CarCar63779CC
07.01.2019CarCar739910AA
08.01.2019CarCar847510AA
09.01.2019CarCar92716CC
10.01.2019RobotsRobots103965BB
11.01.2019RobotsRobots114315BB
12.01.2019RobotsRobots122817AA
14.01.2019BikeBike14913CC
15.01.2019BikeBike21636CC
16.01.2019BikeBike325010AA
17.01.2019BikeBike42455BB
18.01.2019BikeBike52943CC
19.01.2019CarCar64306CC
21.01.2019CarCar83041AA
22.01.2019CarCar93233CC
23.01.2019RobotsRobots102858BB
24.01.2019RobotsRobots114797AA
25.01.2019RobotsRobots124047AA

 

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?

 

  table.jpg

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

avarage for zone B.jpg

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() )?

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_kz Regular Visitor
Regular Visitor

Re: Dax, Average + IsFiltered

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])))
3 REPLIES 3
Community Support Team
Community Support Team

Re: Dax, Average + IsFiltered

Hi @Nick_kz,

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

1.PNG

Could you please offer me more information about it?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Nick_kz Regular Visitor
Regular Visitor

Re: Dax, Average + IsFiltered

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.

 

Nick_kz Regular Visitor
Regular Visitor

Re: Dax, Average + IsFiltered

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