Sum of first values for multiple categories

[Velvetine27]

Hey,

Im looking for a way to get the sum of values for these :

Client	Material	Date	Value
ABC	101	12/1/2022	10
ABC	102	13/1/2022	20
ABC	101	13/1/2022	10
ABC	103	13/1/2022	30

Is there a way for me to get the average value for each clients and sum it all ? Do note that there are tons of other clients with similar condition. Ideally it should take only from the latest date and average it but it works without latest date as well.

Thanks in advance !

[Anonymous]

Hi @Velvetine27 ,

Is this what you want to achieve, calculate the sum and average of the corresponding ones based on the latest date and clients

The following are the relevant measure codes

 Sum_value = SUMX(FILTER(ALL('Table'),'Table'[Client]=SELECTEDVALUE('Table'[Client])&&'Table'[Date]=SELECTEDVALUE('Table'[Date])),'Table'[Value])

Avg_value = AVERAGEX(FILTER(ALL('Table'),'Table'[Client]=SELECTEDVALUE('Table'[Client])&&'Table'[Date]=SELECTEDVALUE('Table'[Date])),[Value])

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[FreemanZ · Accepted solution]

Hi @Velvetine27 

try to create a measure with the code below:

ValueSum =

VAR table1 =

ADDCOLUMNS(

    VALUES(TableName[Client]),

    "Date",

    CALCULATE(MAX(TableName[Date]))

)

VAR Table2 =

CALCULATETABLE(

    TableName,

    TREATAS(

        table1,

        TableName[Client],

        TableName[Date]

    )

)

VAR RESULT =

SUMX(

    VALUES(TableName[Client]),

    AVERAGEX(Table2, TableName[Value])

)

RETURN Result

I tried and it worked like this:

[Velvetine27]

wew this is the first time i see TREATAS. It works perfectly ! thank you for the solution and knowledge imparted. 

[FreemanZ]

With ADDCOLUMNS, we build intermediate tables with expected granularity, but the data lineage is broken. TREATAS serves to rewire the relationship with the original table. 

[Velvetine27]

Hey sorry I just noticed something. This measure ignores the data that isn't available at MAX(Date). Some clients only have data until middle of the month so this measure seems to ignore those that are not available at the MAX(Date). Is there a way we can get the latest date instead ? 

[FreemanZ]

the MAX(Date) is inside ADDCOLUMNS, calculated based on the row context from VALUES([Client]), so this shall not be an issue. Could you say more about the issue?

[FreemanZ]

sum of the average normally doesn't make any sense. what is the purpose?

And what does this mean "works without latest date as well"?

[Velvetine27]

The idea is that we are looking for Value for Client. And the Value for Client is the average for all the Values of Materials under the Client. Thus why I'm looking for the Average to get the Client's value. But at the same time I need to sum all of the Average Values for all Clients.

The Date by right it should take only the latest date of the month. But if we average the value for entire month I assume it wouldnt affect much the Average Value in the end. Sorry if my explanation doesn't make sense. 

[FreemanZ]

average over multiple monthly ends?

would suggest your provide more data, so the advisor could verify the proposed solution. 

[Velvetine27]

Yes, the aggregation should take the final date. So if we display monthly data it should provide latest monthly data for each Client. 

Client	Material	Date	Value
ABC	101	1/1/2022	10
ABC	102	5/1/2022	20
ABC	101	25/1/2022	10
ABC	103	25/1/2022	30
BCD	104	13/1/2022	40
BCD	104	21/1/2022	40
BCD	105	24/1/2022	50
BCD	106	24/1/2022	60

Will this sample data be enough ? The calculation should take : 
for ABC, latest date 25/1/2022 = (10 + 30) / 2 
for BCD, latest date 24/1/2022 = (50 + 60) / 2
final value = 20 + 55