Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculating a quantity from a disctinct count...

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]))

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        Apparently your Quantity is a String?