Calculate Yield

[DIROPS]

I need some assistance, I am trying to calculate the yield of a product (PLU) as it moves through work in progress steps.  The table I am working with lists all the PLU's in on column, the net weight of each PLU in another and the date of the actual transaction to the data capture system in another.  I am trying to figure out how I can visualize the percent difference between finished net weights of two different PLU's in a date range.  Any help would be appreciated.  Sample data attached.  Thanks in advance

sample data

[Anonymous]

Hi @DIROPS ,

To do this, you would need to specify both PLUs. Assuming you would always be comparing 2 only, you could do the following:

1. Create a calculated table with 2 columns, like this:

PLUs = CROSSJOIN(

SELECTCOLUMNS(DISTINCT(PLU_Table[PLU_Column]),"PLU Numerator",[PLU_Column]),

SELECTCOLUMNS(DISTINCT(PLU_Table[PLU_Column]),"PLU Denominator",[PLU_Column])

)

2. Create a calculation:

PLU % Yield =

var num = IF(

HASONEVALUE(PLUs[PLU Numerator]),

CALCULATE(

[Weight Calculation], //SUM(Weight)

FILTER(Store, Store[Store Country] = VALUES(PLUs[PLU Numerator]))

)

,0

)

var den = IF(

HASONEVALUE(PLUs[PLU Denominator]),

CALCULATE(

[Weight Calculation], //SUM(Weight)

FILTER(Store, Store[Store Country] = VALUES(PLUs[PLU Denominator]))

)

,0

)

return DIVIDE(num, den)

For the dates, that should work just by filtering.

Hope this helps,

Nathan

[DIROPS]

Got the first part working, by creat a calculation do you mean create a calculated coulumn in my new table or a measure?

[Anonymous]

@DIROPS  - 2nd part is a measure.

[Anonymous]

Also, the [Weight Calculation] should be a separate Measure, called something like [Total Weight].

[DIROPS]

Firstly, thank you again for your continued guidance, I need all the help I can get!  Hopefully last few questions:

1.  In the second part, I dont understand what this is referencing vs my data, in other words I dont know what to point to here

FILTER(Store, Store[Store Country]

2.  The [Weight Calculation], should this just be a totalization of the Net Weight?

[Anonymous]

1. Ah! Sorry, I tested with my tables, but missed removing that part. It should be filtering the PLU:

FILTER(<PLU Table>, <PLU Table>[<PLU Column>]

2. Yes, create a measure like this: Total Weight = SUM(Weight)

[DIROPS]

Thanks, that seems to have gotten all the measures working, but I am not getting any out put to the visualization, I just keep getting either Blank or 0.00%??

[Anonymous]

Have you selected two PLUs to compare? 

Add a slicer for each of the columns in the calculated table, and select one PLU in each slicer. Does that work?

[DIROPS]

So I am sure its me doing something wrong here, but still only get blank data.  Can you give me a basic rundown on where this data would land in the visualization?  I either get an error, blank or a ridiculous number

[Anonymous · Accepted solution]

It would help to see the calculations as you have them and the visual you're trying to produce. 

That said, here is how I did it - 2 slicer visuals, and 1 card visual:

[DIROPS]

Sorry, had to jump to another project and took me some time to get back to this.  However, as a follow up, I had some relationship errors that was causing my issue, sorted those and running smooth now.  Thanks for the help, really appreciate it!