Average of Text Value

Power BI
1

avg_Text.pbix (36.2 KB)

Dear Experts,
I have a sample data set of employee non- attendance related to SICK and Leave (LVE).
I want to calculate average of each code separately and later Moving average for 07 days.

I have used below DAX but its not working.
average sick = CALCULATE(AVERAGE(Sheet1[Code]),Sheet1[Code]=“Sick”)

Appreciate your assistance and guidance.
Attached PBIX file for your information.

2

@EnanBahadur

I noticed that you’re trying to calculate averages for the “SICK” and “LVE” codes in your PBIX file. However, since the “Code” column contains categorical data (e.g., “SICK” and “LVE”), directly averaging the text values won’t provide meaningful results.

To correctly calculate the averages, you’ll need to first summarize the number of absences for each code. You can achieve this by creating measures, and here are three you can start with:

Number of Absences = CALCULATE(COUNTROWS('Sheet1'))

Average LVE = 
CALCULATE(
    DIVIDE(
        [Number of Absences],
        DISTINCTCOUNT('Sheet1'[Date])
    ),
    'Sheet1'[Code] = "LVE"
)

Average SICK = 
CALCULATE(
    DIVIDE(
        [Number of Absences],
        DISTINCTCOUNT('Sheet1'[Date])
    ),
    'Sheet1'[Code] = "SICK"
)

The “Number of Absences” measure will give you the total count of absences, and the “Average LVE” and “Average SICK” measures will calculate the average number of absences per date for each code.

image

That should get you headed in the right direction.

2 Likes
3

Hi @HufferD

Thank you so much for your guidance and new way of DAX Branching.
Apologies for late reply.