How to create a measure to perform calculations within multiple groups of rows

[selwyn]

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!

[Anonymous · Accepted solution]

Here's a full solution attached.

Best

D

[Anonymous · Accepted solution]

Here's a full solution attached.

Best

D

[Anonymous · Accepted solution]

By the way, if you want to show blank for the measures when no calculation unit (KG or LB) is selected or many at the same time, then you have to remove the default option from SELECTEDVALUE( 'Calculation Unit'[CalculationUnit], "KG"). Just remove the "KG" in this function in all of the measures.

Best
D

[selwyn]

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!

[Anonymous]

I've downloaded the file and it opens without a hitch. Do you have the latest PBI? Please re-download the file and make sure it's been downloaded in full.

Best
D

[selwyn]

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!

[Anonymous]

In PBI THE most important thing is to have a good, logical and simple star-schema model. If you model data the right way, DAX becomes extremely simple and FAST. This is called data-modelling skills 🙂 Without them you'll be writing ugly, long and slow DAX, many a time obtaining numbers you won't even be able to explain.

In a word, always stick to the star-schema if you want to keep your sanity.

If you want, here's something to watch:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

Best
D

[Greg_Deckler]

Yes, but I do not see Good Qty as a column?

[selwyn]

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!