Takes and filters the number of value data then displays the average per day and month

[Bambang_Sono]

hi Please help, I want to create a filter table to find the average cumulative tonnage by adding up the value data first and then displaying the average cumulative per day and month, here is an example from my image: 🙏

[miTutorials · Accepted solution]

Try the below code to create a new calculated table to return the Cumulative Avg Value.

CUMULATIVE_AVG_TABLE = 
ADDCOLUMNS(
    SUMMARIZE('Table', 'Table'[DATE], 'Table'[MONTH]),
    "SUM TONASE", CALCULATE(SUM('Table'[TONASE])),
    "AVG TONASE",
    VAR CurrentDate = 'Table'[DATE]
    VAR CurrentMonth = 'Table'[MONTH]

    VAR CumulativeTonnage =
        CALCULATE(
            SUM('Table'[TONASE]),
            FILTER(
                'Table',
                'Table'[DATE] <= CurrentDate &&
                'Table'[MONTH] = CurrentMonth
            )
        )

    VAR DaysCount =
        CALCULATE(
            DISTINCTCOUNT('Table'[DATE]),
            FILTER(
                'Table',
                'Table'[DATE] <= CurrentDate &&
                'Table'[MONTH] = CurrentMonth
            )
        )

    RETURN 
        DIVIDE(CumulativeTonnage, DaysCount)
)

[Bambang_Sono]

hi @miTutorials For example how do you measure it?

[Bambang_Sono]

hi @miTutorials thank solved

[girishthimmaiah]

1. Create a Measure to Calculate Daily Sum of Tonnage
First, you will need to aggregate the weight for each date.

DAX Measure:
DAX
Copy
Daily Sum Tonase =
CALCULATE(
SUM('YourTable'[TONASE]),
ALLEXCEPT('YourTable', 'YourTable'[DATE])
)
2. Create Cumulative Average Measure
And then, you will get a cumulative average measure, calculating the accumulated average of daily tonnage by its accumulated sum divided by the number of days.

DAX Measure for Cumulative Average:
DAX
Copy
Cumulative Avg Tonase =
CALCULATE(
AVERAGEX(
FILTER(
ALLSELECTED('YourTable'),
'YourTable'[DATE] <= MAX('YourTable'[DATE])
),
[Daily Sum Tonase]
),
ALLEXCEPT('YourTable', 'YourTable'[MONTH])
)
Measures Explained:
Daily Sum Tonase: Sums the tonnage for every date to match your image under "SUM VALUE".
Cumulative Avg Tonase: The running (cumulative) average weight for tonnage per day, reset by Month.