How to create a measure that only displays one value for each row on a specific column of data

Anonymous — Sun, 22 May 2022 20:21:49 GMT

Apologies, it is a difficult question to ask so I will explain it the best I can.

I have a visual that shows the total of a column called "Quantity" from the table [Line Items], however, there is another column on the [Line Items] table called “Category”. These categories are typically “Assembly”, “Workshop”, and “Virtual”.

Here's a sample of that table [Line Items] below:

Line Item ID	Deal ID	Category	Quantity	Amount
1001	1004	Assembly	60	200
1002	1003	Workshop	260	550
1003	1003	Workshop	260	398
1004	1004	Assembly	60	800
1005	1003	Virtual	260	105

These line items are attatched to different Deals, and on every Deal I only want one quantity value (the quantity values will always be the same on a deal) as you can see above.

So, each quantity has a category. I want to write a measure that only calculates the quantity from one category rather than all 3. So, if my table visual shows a specific set of [Line Items] and it has quantity categories that are assembly and workshop, I want it to only take the quantity value from one or the other, not both.

So if I filter a visual that shows lineitems 1002, 1003, and 1005 that is from Deal 1003, I want the quantity to remain 260 rather than adding the quantity up. However, I still want the amount values calculated normally, so I only want the Quantity column effected.

So if I was to create say an "Official Quantity" measure, how would it go?

Official Quantity =

Re: How to create a measure that only displays one value for each row on a specific column of data

Anonymous — Mon, 23 May 2022 08:14:01 GMT

Apologies this post was marked as spam previously I'm not sure why.

Re: How to create a measure that only displays one value for each row on a specific column of data

Anonymous — Tue, 24 May 2022 10:46:18 GMT

I found a way how to do it, incase anyone else needs to go, here is the measure I created:

Official Quantity = SUMX (DISTINCT('LineItem'[quantity]), FIRSTNONBLANK ('LineItem'[quantity], 0 ))

topic Re: How to create a measure that only displays one value for each row on a specific column of data in DAX Commands and Tips

How to create a measure that only displays one value for each row on a specific column of data

Re: How to create a measure that only displays one value for each row on a specific column of data

Re: How to create a measure that only displays one value for each row on a specific column of data