topic Sum a column using filters relating to a distinct value in another column in DAX Commands and Tips

Sum a column using filters relating to a distinct value in another column

Anonymous — Fri, 28 Oct 2022 13:21:40 GMT

I have a table containing columns for passengers, passenger class, ticket no. and cost of ticket. However, where the ticket is for more than one passenger the total cost of the ticket has been included for each passenger as per the sample table

Passenger	Ticket	Class	Cost
1	A1	A	20
2	A2	A	21
3	A3	A	39
4	A3	A	39
5	A4	B	15
6	A5	B	42
7	A5	B	42
8	A5	B	42
9	A6	C	10
10	A7	C	9

I am trying to find the average ticket price per class so need to filter the cost column by class and just sum the first occurrence of the cost per ticket (I can't sum the first occurrence of the cost as in my actual table some ticket costs are the same for other tickets).

I can use FILTER to seperate the class but I can't work out how to include only one value per ticket.

I have tried so many versions to sum up the cost column correctly this being my last attempt -

TEST = Calculate(SUMX('Sample','Sample'[Cost]),DISTINCT('Sample'[Ticket]),FILTER('Sample','Sample'[Class] = "A"))

but it returns the equivalent of 119 from the table above rather than 89.

Can someone help please?

Re: Sum a column using filters relating to a distinct value in another column

Anonymous — Fri, 28 Oct 2022 13:49:05 GMT

Sorry that's '80' not '89'!

Re: Sum a column using filters relating to a distinct value in another column

Jihwan_Kim — Fri, 28 Oct 2022 13:50:23 GMT

Hi,

Please check the below picture and the attached pbix file whether it suits your requirement.

Cost total distinct: = SUMX ( DISTINCT ( Data[Ticket] ), CALCULATE ( MAX ( Data[Cost] ) ) )

Re: Sum a column using filters relating to a distinct value in another column

DimaMD — Fri, 28 Oct 2022 13:58:15 GMT

Anonymous try it

Re: Sum a column using filters relating to a distinct value in another column

Anonymous — Fri, 28 Oct 2022 13:59:28 GMT

Sorry Jihwan_Kim but what I ultimately need is

A - 80 / 4 = 20

B - 57 / 4 = 14.25

C - 19 / 2 = 9.5

Re: Sum a column using filters relating to a distinct value in another column

Anonymous — Fri, 28 Oct 2022 14:07:11 GMT

Can you just advise how you filtered by class. That is the right sum for Class As but how do I get the same for B and C?

Re: Sum a column using filters relating to a distinct value in another column

DimaMD — Fri, 28 Oct 2022 14:09:12 GMT

Anonymous try it

Re: Sum a column using filters relating to a distinct value in another column

Anonymous — Fri, 28 Oct 2022 14:17:03 GMT

That's the correct sums per Class for my sample table so I'm just going to try it on my actual table and will get back to you.

Re: Sum a column using filters relating to a distinct value in another column

Anonymous — Fri, 28 Oct 2022 14:31:53 GMT

I've just tried it on my actual table and it has returned just one result which I believe is the average for the last class (equivalent to Class C).

Re: Sum a column using filters relating to a distinct value in another column

DimaMD — Fri, 28 Oct 2022 14:35:29 GMT

Anonymous provide example data please to understand the calculation

Re: Sum a column using filters relating to a distinct value in another column

Anonymous — Fri, 28 Oct 2022 15:17:28 GMT

This is just a small sample of my data with all other irrelevant columns deleted but I think it includes examples of multiple and single passenger tickets across all classes. Incidently when I tried SUMX(SUMMARIZE('table','table'[Class],'table'[Cost]),[Cost]) it returned

a too few arguments error so I'm not sure where I went wrong there either!

Passenger	Fare	Ticket	Class
ABBING42	7.55	5547	3rd Class Passenger
ABBOTT13	20.25	2673	3rd Class Passenger
ABBOTT16	20.25	2673	3rd Class Passenger
ABBOTT35	20.25	2673	3rd Class Passenger
ABELSETH16	7.13	348125	3rd Class Passenger
ABELSETH25	7.13	348122	3rd Class Passenger
ABELSON28	24	3381	2nd Class Passenger
ABELSON30	24	3381	2nd Class Passenger
ABI SA'B45	7.46	2685	3rd Class Passenger
ABI SHADID19	7.46	2698	3rd Class Passenger
ABRAHAMSSON19	7.186	3101284	3rd Class Passenger
AKS18	9.35	392091	3rd Class Passenger
AL-BA'QLINI2	19.2583	2666	3rd Class Passenger
AL-BA'QLINI23	19.2583	2666	3rd Class Passenger
AL-BA'QLINI3	19.2583	2666	3rd Class Passenger
AL-BA'QLINI5	19.2583	2666	3rd Class Passenger
ALBIMONA26	18.7875	2699	3rd Class Passenger
ALLISON0.92	151.55	113781	1st Class Passenger
ALLISON2	151.55	113781	1st Class Passenger
ALLISON25	151.55	113781	1st Class Passenger
ALLISON30	151.55	113781	1st Class Passenger
ALLUM18	8.3	2223	3rd Class Passenger
ANDERSSON17	7.925	3101281	3rd Class Passenger
ANDERSSON26	7.156	347075	3rd Class Passenger
ANGLE31	26	226875	2nd Class Passenger
ANGLE36	26	226875	2nd Class Passenger
ASPLUND13	31.3875	347077	3rd Class Passenger
ASPLUND23	7.1511	350054	3rd Class Passenger
ASPLUND3	31.3875	347077	3rd Class Passenger
ASPLUND38	31.3875	347077	3rd Class Passenger
ASPLUND40	31.3875	347077	3rd Class Passenger
ASPLUND5	31.3875	347077	3rd Class Passenger
ASPLUND6	31.3875	347077	3rd Class Passenger
ASPLUND9	31.3875	347077	3rd Class Passenger
ASSAM23	7.1	3101309	3rd Class Passenger
ASTOR18	227.525	17757	1st Class Passenger
ASTOR47	227.525	17757	1st Class Passenger
ATTALAH17	14.4583	2627	3rd Class Passenger
ATTALAH30	7.225	2694	3rd Class Passenger
AUBART24	69.3	17477	1st Class Passenger

Re: Sum a column using filters relating to a distinct value in another column

DimaMD — Mon, 31 Oct 2022 07:11:47 GMT

Hi, Anonymous Substituted the data you provided, review the file to see if this is your desired result

Re: Sum a column using filters relating to a distinct value in another column

Anonymous — Mon, 31 Oct 2022 09:23:13 GMT

Thanks so much that works now. As a newbie I have not yet come across the more detailed formula you used. I need to do more studying!

Re: Sum a column using filters relating to a distinct value in another column

DimaMD — Mon, 31 Oct 2022 10:15:45 GMT

Anonymous I wish you success and a good experience, everything will be fine