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,
Is it possible to create a DAX measure in Power BI that calculates the Actual Scrap Weight from this query?
Here's a screenshot of the query for easy reference. And also the sample data.
Source data:
Some things to note about this query:
1. The [Plant WtUnit] column indicates the type of measurement unit (KG or LB) used at the respective plants.
2. Each Order Ref consists of 3 attributes, namely Backflush, Good Qty, and Rejected Qty:
Backflush - The WEIGHT (KG or LB) of the total amount of material used.
Good Qty - The QUANTITY (pcs) of good finished goods.
Rejected Qty - The QUANTITY (pcs) of rejected finished goods.
3. The corresponding values (under the [Value] column) of each of these attributes will hence refer to either a weight quantity (KG or LB) or a count (no. of pcs). Therefore the Value for the Backflush attribute is either KG or LB, while the Values for the Good Qty and Rejected Qty attributes refers to the number of pieces.
4. GW refers to Gross Weight, NW refers to Net Weight.
Intended Outcome:
As mentioned earlier, I would like to create a DAX measure in Power BI to calculate the Actual Scrap Weight (of each Order Ref), and this is how it is calculated (in a non-DAX manner):
Actual Scrap Weight = Backflush - Good Qty Weight
where
Good Qty Weight = Good Qty x NW per pc
So for example, for Order Ref A1001:
Good Qty Weight (in KG) = Good Qty x NW per pc (KG)
= 1000 x 2KG
= 2000KG
Hence,
Actual Scrap Weight (in KG) = Backflush - Good Qty Weight
= 2130KG - 2000KG
= 130KG
Can this be achieved through the use of DAX alone? Or are further transformations in Power Query needed first? Or are there more than two ways to skin ... an onion?
Thanks!
Solved! Go to Solution.
Hi Daryl,
Thanks so much for helping to work on my issue. I've downloaded your pbix but am unable to open it as I'm greeted with the "Object reference not set to an instance of an object" error msg each time I try to open the file.
If it isn't too much trouble, is there a way for the syntax of your solution to be posted on this thread directly, and then I'll try cobbling it up with my data set.
Thanks again for your help!
Thanks @Anonymous!
You're right, my Power BI wasn't the latest version. Your file opened without a hitch after I updated my Power BI and your solution worked like a charm!
On a related note, I thought your approach to the problem was pretty unique - effective and uncomplicated - and that it also taught me a lesson on learning to see things from different perspectives/angles.
Thanks so much and kudos to you!
Thanks for the quick response, @Greg_Deckler
Yup, it doesn't stand alone as a column but is actually part of the Attribute column.
I don't know if the query in its current iteration is ideal though, does it need further transformation?
Thanks!
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |