topic Re: Summarize value in a column with empty cells, filtered by other unique value from another column in DAX Commands and Tips

Summarize value in a column with empty cells, filtered by other unique value from another column

LogiFons — Fri, 08 Dec 2023 11:49:59 GMT

Hello all,

I am searching in severall articles a solution for my issue.

I would like to display in a card the sum of points by week.

Trip	Points	Week
1001	3	1
1001	3	1
1002		2
1002		2
1005	2	1
1005	2	1
1010	1	4

So i need the unique Trip numbers and the summarize of the points.

In some cases a trip has no points and stays empty.

Trip	Points	Week
1001	3	1
1002		2
1005	2	1
1010	1	4

When i use the week filter in my dashboard, selecting week 1, the card must show me 5 Points.

The best working measure for me was this one, but didn't give the correct solution.

Measure = SUMX(SUMMARIZE(TblPoints, TblPoints[Trip], TblPoints[Points]), TblPoints)

What do i miss?

Gr,

Re: Summarize value in a column with empty cells, filtered by other unique value from another column

Fowmy — Fri, 08 Dec 2023 12:23:44 GMT

LogiFons

Use this measure:

Total Points = SUMX( VALUES( Table18[Trip]), CALCULATE( MAX( Table18[Points] ) ) )

Re: Summarize value in a column with empty cells, filtered by other unique value from another column

LogiFons — Fri, 08 Dec 2023 13:45:57 GMT

Hello Fowmy,

This has the same solution as my formula.

So i think there is something in my data sheet wrong.

I wil find out first.

Thank you!

Re: Summarize value in a column with empty cells, filtered by other unique value from another column

Anonymous — Wed, 13 Dec 2023 01:54:44 GMT

Hi LogiFons

I tested it with the data you gave me, tried the method you described, then selected week 1 in slicer, and got the result you wanted, I'm not sure if you did something else and got the wrong result. Can you provide more information? Please remove any sensitive data in advance.

Best Regards,

Community Support Team _Yuliax

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

Re: Summarize value in a column with empty cells, filtered by other unique value from another column

LogiFons — Wed, 13 Dec 2023 07:03:32 GMT

Good morning,

I had some wromhg values in my table.

Now i am usinhg the measure of Fowny and thid is working.

Thank you both of your support.