topic Re: Sum of first values for multiple categories in DAX Commands and Tips

Sum of first values for multiple categories

Velvetine27 — Sun, 27 Nov 2022 23:30:35 GMT

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 !

Re: Sum of first values for multiple categories

FreemanZ — Mon, 28 Nov 2022 01:50:58 GMT

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"?

Re: Sum of first values for multiple categories

Velvetine27 — Mon, 28 Nov 2022 02:04:37 GMT

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.

Re: Sum of first values for multiple categories

FreemanZ — Mon, 28 Nov 2022 03:31:50 GMT

average over multiple monthly ends?

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

Re: Sum of first values for multiple categories

Velvetine27 — Mon, 28 Nov 2022 04:16:13 GMT

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

Re: Sum of first values for multiple categories

Anonymous — Mon, 28 Nov 2022 07:31:44 GMT

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.}

Re: Sum of first values for multiple categories

FreemanZ — Mon, 28 Nov 2022 13:42:56 GMT

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:

Re: Sum of first values for multiple categories

Velvetine27 — Tue, 29 Nov 2022 01:29:53 GMT

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

Re: Sum of first values for multiple categories

FreemanZ — Tue, 29 Nov 2022 02:49:22 GMT

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.

Re: Sum of first values for multiple categories

Velvetine27 — Tue, 29 Nov 2022 08:30:59 GMT

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 ?

Re: Sum of first values for multiple categories

FreemanZ — Tue, 29 Nov 2022 09:19:55 GMT

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?