DAX Category - Avg Sales month

[Anonymous]

Hi Every one,

I have data like below

Date	Category	Values
01-01-2017	AH M	97
01-01-2017	AH N	88
01-01-2017	AH P	87
01-01-2017	UBS	97
01-01-2017	YFI	68
01-01-2017	UUU	78
01-02-2017	AH M	81
01-02-2017	AH N	64
01-02-2017	AH P	64
01-02-2017	UBS	83
01-02-2017	YFI	78
01-02-2017	UUU	74
01-03-2017	AH M	94
01-03-2017	AH N	75
01-03-2017	AH P	74
01-03-2017	UBS	60
01-03-2017	YFI	95
01-03-2017	UUU	91
01-04-2017	AH M	63
01-04-2017	AH N	90
01-04-2017	AH P	99
01-04-2017	UBS	92
01-04-2017	YFI	80
01-04-2017	UUU	80
01-05-2017	AH M	62
01-05-2017	AH N	76
01-05-2017	AH P	75
01-05-2017	UBS	99
01-05-2017	YFI	63
01-05-2017	UUU	78

Output file should be like below

Category	Values
AH M	0.794
AH N	0.786
AH P	0.798
UBS	0.862
YFI	0.768
UUU	0.802

I have writen formual in excel (=(SUM(Balance!C2,Balance!C8,Balance!C14,Balance!C20,Balance!C26)/5)/100)

i want to wirte same funtion in "DAX" - can any body help on this.

Thanks in advance.....

regards

Venu

[v-chuncz-msft · Accepted solution]

@Anonymous ,

Check the measure below as well.

Measure =
AVERAGEX ( VALUES ( Table1[Date] ), CALCULATE ( SUM ( Table1[Values] ) ) ) / 100

[ElenaN]

Hello,

You can create a new measure = AVERAGE([Values])/100 and format it to show 3 digits.

Regards,

ElenaN

[Anonymous]

@ElenaN  Thanks for your response, but this not right way...

[v-chuncz-msft · Accepted solution]

@Anonymous ,

Check the measure below as well.

Measure =
AVERAGEX ( VALUES ( Table1[Date] ), CALCULATE ( SUM ( Table1[Values] ) ) ) / 100

[Anonymous]

@v-chuncz-msft Thanks for your response..

i explain what i am expecting..... for example: i need to calculate "AH M" category avg (process like below)

01-01-2017	AH M	97
01-02-2017	AH M	81
01-03-2017	AH M	94
01-04-2017	AH M	63
01-05-2017	AH M	62
	Total	397
	Avg	79.4
	Avg / 100	0.794

1. First i need to do sum (=SUM(I2:I6))

2. Second need to do AVG (=I7/5)

3. third need to do AVG / 100 (=I8/100)

this is my approach, i need category wise avg output should come like below.....

Category	Values
AH M	0.794
AH N	0.786
AH P	0.798
UBS	0.862
YFI	0.768
UUU	0.802

Can anybody help on this..... Thanks in advance....

Regards

Venu

[Anonymous]

Anybody help on this...

[v-chuncz-msft · Accepted solution]

@Anonymous ,

Just drag the measure into a table visual.

[Anonymous]

@v-chuncz-msft  table level values has come perfect,  but total sum has come incorrect. 

can you help on this....

Regards

Venu