Calculating a quantity from a disctinct count...

[ballist1x]

Hi i am relatively new to powerBI and am trying to work out how i do the following:

i have an order number (many hundrreds of thousands) of which some are duplicated and some are not. Each order number has a quantity.

I need to work out the total number of quantity ordered, dedpulicating the order numbers.

However i cant just dedupe the order numbers as thye duplicates contain other items of data that are needed elsewhere in the report.

Data format is

Order number:

123456

Quantity

123

Any help would be great!

i tried this but it didnt work

Measure = CALCULATE(sum(TABLE[Quantity],FILTER(DISTINCT(TABLE[ORDER NUMBER]))

[Greg_Deckler]

There are a number of ways you could do this. One way would be to just use an AVERAGE of Quantity. You put that into a table visualization, which will automatically only show distinct values and you would be done.

You could also create a measure like this:

Measure 2 = SUMX(SUMMARIZE(DISTINCT('TABLE'[Order number]),'TABLE'[Order number],"Quantity", AVERAGE('TABLE'[Quantity])),[Quantity])

You could also import from the same data source and remove "Order number" duplicates. This would go into its own table (second table) and then you could just sum the Quantity there.

Probably a couple other ways to solve it.

[ballist1x]

Hi unforutnately it didnt work. the formula is without error however in the visualisation i get:

Feedback Type:
Frown (Error)

Timestamp:
2018-02-19T13:28:32.5388296Z

Local Time:
2018-02-19T13:28:32.5388296+00:00

Session ID:
4ec7ae7e-7017-4c9e-adbf-67ac830bd579

Release:
December 2017

Product Version:
2.53.4954.621 (PBIDesktop) (x64)

Error Message:
MdxScript(Model) (4, 121) Calculation error in measure 'H12015'[DEDUPE QTY]: The function AVERAGE cannot work with values of type String.

OS Version:
Microsoft Windows NT 10.0.14393.0 (x64 en-US)

[Greg_Deckler · Accepted solution]

Apparently your Quantity is a String?

[ballist1x]

Hi this is resolved now once id set the quantity to a decimal number!

thanks

[ballist1x]

thanks ill try now.

As the database is quite large adding another table is something i tried but doubles the file size to 300+MB.