Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
Solved! Go to Solution.
Apparently your Quantity is a String?
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.
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)
Apparently your Quantity is a String?
Hi this is resolved now once id set the quantity to a decimal number!
thanks
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |