topic Re: DAX to calculate average benchmark of the ratings in DAX Commands and Tips

DAX to calculate average benchmark of the ratings

deb_power123 — Thu, 15 Jul 2021 14:03:53 GMT

Hi All,

The rating of a vendor company is marked from the range 5 to -5 by customers as in the below source table. Rating column defines the rating per company per date and this date is for the whole year .I have added as a sample data for date in the below given range.

I need to find the average Benchmark for ratings of the total companies[A,B,C,D] as stated below :-

How can i find the average benchmark number for rating of the total companies in the market [A,B,C,D]?

Could anyone suggest any DAX calculated measure or column to find this average benchmark in the below case scenario?

Rating	Company	Date
2	A	14.07.2021
2	A	15.07.2021
2	A	13.07.2021
1	A	12.07.2021
-1	A	11.07.2021
0	A	10.07.2021
1	B	14.07.2021
1	B	15.07.2021
4	B	13.07.2021
2	B	12.07.2021
2	B	11.07.2021
-1	B	10.07.2021
1	C	14.07.2021
1	C	15.07.2021
4	C	13.07.2021
-2	C	12.07.2021
2	C	11.07.2021
3	C	10.07.2021
1	D	12.07.2021
4	D	11.07.2021
3	D	10.07.2021

Kind regards

Sameer

Re: DAX to calculate average benchmark of the ratings

Anonymous — Thu, 15 Jul 2021 16:38:38 GMT

[Benmchark Method 1] = // This will return the average // of average company benchmarks. AVERAGEX( DISTINCT( T[Company] ), CALCULATE( AVERAGE( T[Rating] ) ) ) [Benmchark Method 2] = // This will return the average // of benchmarks for all the // visible companies. AVERAGE( T[Rating] )

Re: DAX to calculate average benchmark of the ratings

deb_power123 — Fri, 16 Jul 2021 12:40:53 GMT

Hi Anonymous

Thankyou for your help, it worked.

I need another DAX here . Since current month is July and it is not completed so whatever is the current date say 16jul-2021 we need to take range till 16-Jun2021 for 30 days to complete the month.We need to find the current month reputation average say today is 16-07-2021 to 16-06-2021 is the current rating average KPI and then from 15-06-2021 to 16-05-2021 is previous month rating average KPI.

Means I need to find the current month average rating KPI for the companies and previous month average rating KPI.Could you please suggest any DAX to handle this ?

My Source :

Rating	Comapany	Date
2	A	16.07.2021
2	A	15.07.2021
2	A	13.07.2021
1	A	12.07.2021
-1	A	11.07.2021
0	A	10.07.2021
1	B	16.07.2021
1	B	15.07.2021
4	B	13.07.2021
2	B	12.07.2021
2	B	11.07.2021
-1	B	10.07.2021
1	C	16.07.2021
1	C	15.07.2021
4	C	13.07.2021
-2	C	12.07.2021
2	C	11.07.2021
3	C	10.07.2021
1	D	12.06.2021
4	D	11.06.2021
3	D	10.06.2021
1	B	10.06.2021
4	B	14.06.2021
2	B	15.06.2021
2	B	13.06.2021
-1	B	12.06.2021
1	C	14.06.2021
1	C	10.05.2021
4	C	12.05.2021
-2	C	11.05.2021
2	C	10.05.2021
3	C	11.05.2021
1	D	10.05.2021
4	D	11.05.2021