How to get daily average based on string value and with the number of days showing in stacked chart

[Anonymous]

i have data with strings value showing case number which i calculate as count to get total case per day =count(Case_number_old__). from this number i would like to see average daily on chart that if i filter by week 26, it will get an average the days showing in the chart. for example if the days showing 2days only on that week, it will calculate only these 2days and if the number of days changing to 7days, it will average based on these 7 days. 

Hope someone can help me on this. Thanks

You may refer to the screenshot below on week that i use for filter:

Week 26 with 7daysWeek 27 with 2days

[Anonymous]

Hi @Anonymous ,

Thanks for the reply fom aduguid .

Here is the data table and date table I created.

Date	Sale
6/20/2024 12:00:00 AM	170
6/21/2024 12:00:00 AM	280
6/22/2024 12:00:00 AM	150
6/23/2024 12:00:00 AM	260
6/24/2024 12:00:00 AM	300
6/25/2024 12:00:00 AM	320
6/26/2024 12:00:00 AM	180
6/27/2024 12:00:00 AM	160
6/28/2024 12:00:00 AM	260
6/29/2024 12:00:00 AM	340
6/30/2024 12:00:00 AM	320
7/1/2024 12:00:00 AM	290
7/2/2024 12:00:00 AM	170
7/3/2024 12:00:00 AM	150

Date
6/20/2024 12:00:00 AM
6/21/2024 12:00:00 AM
6/22/2024 12:00:00 AM
6/23/2024 12:00:00 AM
6/24/2024 12:00:00 AM
6/25/2024 12:00:00 AM
6/26/2024 12:00:00 AM
6/27/2024 12:00:00 AM
6/28/2024 12:00:00 AM
6/29/2024 12:00:00 AM
6/30/2024 12:00:00 AM
7/1/2024 12:00:00 AM
7/2/2024 12:00:00 AM
7/3/2024 12:00:00 AM

The two tables are disconnected.

Create a measure:

Measure = CALCULATE(AVERAGE('Table'[Sale]),FILTER(ALL('Table'),'Table'[Date] IN VALUES('Date'[Date])))

You can see that it works fine and calculates the average value based on the selected days.

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Anonymous]

Hi @Anonymous , this can be done if the sales column value is numeric. But consider that sales are in a string value in my case the column Case_Number_OLD__c is a combination alphabet and numeric refers to a case. So when, i tried to change the Case_Number_OLD__c to value as 1 and sum all this, using the measures you've provided, the value will be shown as average 1 which is incorrect. looking at the total each day is 423 divided by 7days, average daily should be 60. Additionally, there is filtering data considering this chart as I filter the case category to Complaint and range to Week 26. 

[Anonymous]

Hi @aduguid , Thanks for your suggestion. I've tried to use the dax measure but it seems to return to value 1 as per below. looking at the total each day is 423 divided by 7days, average daily should be 60. Additionally, there is filtering data considering this chart as I filter the case category to Complaint and range to Week 26. 

Here are some detailed column that I have in the 'Case' table

Hope you can assist with this. Thanks

[aduguid]

My appologies, give this one a try

AverageDailyCases = 
IF (
    ISFILTERED('YourDateTable'[Date]),
    AVERAGEX(
        VALUES('YourDateTable'[Date]),
        [DailyCaseCount]
    ),
    BLANK()
)

[Anonymous]

Hi @aduguid , thanks for the revised measure. I tried and the return value now becomes blank as below.

[aduguid]

Try these DAX measures

DailyCaseCount = COUNT('YourTable'[Case_number_old__])

AverageDailyCases = 
VAR NumberOfDays =
    COUNTROWS(
        VALUES('YourDateTable'[Date])
    )
VAR TotalCases =
    SUMX(
        VALUES('YourDateTable'[Date]),
        [DailyCaseCount]
    )
RETURN
    IF(NumberOfDays > 0, TotalCases / NumberOfDays, 0)