Need help with averagex , as its not showing the right amount in my visual

[Ishallown]

hi everyone,

i have an issue with my power BI visual , in the table below as you can see its showing 0.16552 , yet in the graph for the month of August its showing 0.21741 , both of them are from the same measure yet they are showing different results

*Do note the one covered in black is just a customer number*

 my code as below for the ECL USD SMA:

ECL USD SMA =

AVERAGEX(

FILTER(vw_1_ECL,

[Report Run Date]<=MAX([Report Run Date])),

vw_1_ECL[ECL USD])

[amitchandak]

@Ishallown , the formula seems fine. but why your cumulative average is always increasing. Are using date table for display of month year. try formula like these with a date table

ECL USD SMA =

AVERAGEX(

FILTER(Date,

Date[Date]<=MAX([Report Run Date])),

vw_1_ECL[ECL USD])

or

ECL USD SMA =

AVERAGEX(

FILTER(Date,

Date[Date]<=MAX(Date[Date])),

vw_1_ECL[ECL USD])

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Ishallown]

hi! 
below is the sample data
i would like to show by country (legend) the moving averages (value ) and the shared axis being the report run date.

Payer Number	CountryCode	Report Run Date	TotalExposureUSD
1	SK01	31-08-20	0.022227
1	SK01	28-08-20	0.022227
1	SK01	26-08-20	0.022227
1	SK01	25-08-20	0.022227
1	SK01	21-08-20	0.022227
1	SK01	19-08-20	0.022227
1	SK01	17-08-20	0.022227
1	SK01	14-08-20	0.022227
1	SK01	12-08-20	0.022227
1	SK01	10-08-20	0.022227
1	SK01	07-08-20	0.022227
1	SK01	05-08-20	0.022227
1	SK01	03-08-20	0.022227
1	SK01	31-07-20	0.01667
1	SK01	29-07-20	0.01667
1	SK01	27-07-20	0.01667
1	SK01	22-07-20	0.01667
1	SK01	20-07-20	0.01667
1	SK01	17-07-20	0.01667
1	SK01	15-07-20	0.01667
1	SK01	13-07-20	0.01667
1	SK01	10-07-20	0.01667
1	SK01	08-07-20	0.01667
1	SK01	06-07-20	0.01667
1	SK01	03-07-20	0.01667
1	SK01	01-07-20	0.01667
1	SK01	29-06-20	0.011113
1	SK01	24-06-20	0.011113
1	SK01	22-06-20	0.011113
1	SK01	19-06-20	0.011113
1	SK01	17-06-20	0.011113
1	SK01	15-06-20	0.011113
1	SK01	12-06-20	0.011113
1	SK01	10-06-20	0.011113
1	SK01	08-06-20	0.011113
1	SK01	01-06-20	0.011113
1	SK01	29-05-20	0.005557
1	SK01	22-05-20	0.005557
1	SK01	15-05-20	0.005557
1	SK01	08-05-20	0.005557
1	SK01	30-04-20	0
1	SK01	24-04-20	0
1	SK01	16-04-20	0
1	SK01	07-04-20	0
2	SK01	03-07-20	0.270229
2	SK01	06-07-20	0.270229
2	SK01	08-07-20	0.270229
2	SK01	10-07-20	0.32537
2	SK01	13-07-20	0.32537
2	SK01	22-07-20	0.370226
2	SK01	15-07-20	0.32537
2	SK01	27-07-20	0.370226
2	SK01	17-07-20	0.370226
2	SK01	29-07-20	0.370226
2	SK01	07-04-20	0
2	SK01	16-04-20	0
2	SK01	24-04-20	0
2	SK01	30-04-20	0
2	SK01	08-05-20	0.005557
2	SK01	15-05-20	0.005557
2	SK01	22-05-20	0.057414
2	SK01	29-05-20	0.057414
2	SK01	01-06-20	0.06297
2	SK01	08-06-20	0.11458
2	SK01	10-06-20	0.11458
2	SK01	12-06-20	0.11458
2	SK01	15-06-20	0.163029
2	SK01	19-08-20	0.412584
2	SK01	17-06-20	0.163029
2	SK01	19-06-20	0.213209
2	SK01	22-06-20	0.213209
2	SK01	24-06-20	0.213209
2	SK01	29-06-20	0.264672
2	SK01	01-07-20	0.270229
2	SK01	20-07-20	0.370226
2	SK01	31-07-20	0.370226
2	SK01	03-08-20	0.412584
2	SK01	05-08-20	0.412584
2	SK01	07-08-20	0.412584
2	SK01	10-08-20	0.412584
2	SK01	12-08-20	0.412584
2	SK01	14-08-20	0.412584
2	SK01	17-08-20	0.412584
2	SK01	21-08-20	0.412584
2	SK01	25-08-20	0.412584
2	SK01	26-08-20	0.412584
2	SK01	28-08-20	0.412584
2	SK01	31-08-20	0.412584
4	IN02	01-06-20	0.1234
4	IN02	02-06-20	0.564
4	IN02	03-06-20	0.225
4	IN02	07-07-20	0.1553
4	IN02	08-07-20	0.169
4	IN02	09-07-20	0.122
4	IN02	01-08-20	0.1365
4	IN02	02-08-20	0.188
4	IN02	03-08-20	0.889
4	IN02	04-08-20	0.954
4	IN02	05-08-20	0.44

thanks for the help

[AntrikshSharma]

Because at the Grand Total of the matrix you have all the dates visible for the AVERAGEX where as in the chart you are only filtering by one month, there is a difference in both.

Also you are not removing filters from vw_1_ECL using ALL ( vw_1_ECL ) so at each row of the report only one month will be visible and that will be the month returned by MAX too, you can verify this by removing "=" before MAX.

You should use Dates, Month & Year from the dates table and not from a fact table.

[Ishallown]

Hi Sharma ,

thanks for the reply , i was wondering then how would i able to calculate a simple moving average and also show the averages in the visual over a period of Months?  
im quite new to power BI thats why im not really familiar with what you said 😕