Assistance- To Compare a value against Total in a matrix

[Jaweed]

Hi all

I have a Table in Excel with these columns:

Company     Date              Criteria                   Count

CPYA            Jan 24            New Joiners           1

CPYA            Feb 24          New Joiners             10

CPYA            Mar 24          New Joiners           7

I have done a matrix, which is working fine, showing the company as row, and New Joiners as Column. Actually I have lots of companies and many criteria in the table.  For simplicity, I 

Question:  How to calculate the average and compare each row with the average, If Row value is  < Average( Count ),  then NewMeasure = 0 , else = 1. In this case Average Count= 18/3 = 6

Matix is like

 CPY A                              Count     Average    New Measure ( Looking for help)

     Jan 24                            1                                           0

     Feb 24                          10                                          1

    March 24                         7                                          1

Total                                   18             6                          1

 Thanks

[Sahir_Maharaj · Accepted solution]

Hello @Jaweed,

Can you please try this approach:

1. Calculate the Average Count

Average Count = 
AVERAGEX(
    VALUES('Table'[Date]),
    CALCULATE(SUM('Table'[Count]))
)

2. Create the New Measure

NewMeasure = 
VAR CurrentCount = SUM('Table'[Count])
VAR AvgCount = [Average Count]
RETURN IF(CurrentCount < AvgCount, 0, 1)

[Jaweed]

@Sahir_Maharaj , Thank you so much Sir. Working properly. Only thing is that I cannot interprete my Grand Total value.