Dividing Total Kgs per Process Order according to its Nominal Speed

[GuerauFF]

Hi there,

Here's my case, it is a little bit dense but I'll try to explain it as plainly as possible. 

I would like to calculate a variable called GoodProdTime. According to my theoretical formula, GoodProdTime = Kg of good production / Nominal Speed. The problem that I am facing is that this calculus should be done independently per each process order (PO), as each process order will have a different amout of kgs of good production but also a different nominal speed. To put it in a formula this is what I would like to do (according to a filter of time that I'm applying):

As of now, what I'm able to calculate is the following one:

The way I calculate this second formula is the following one: 

GoodProdTime = 
CALCULATE(SUM('ng SC_EGR_EGR_BAT'[QuantityFP]),'ng SC_EGR_EGR_BAT'[Reverse] = "", FORMAT('ng SC_EGR_EGR_BAT'[Rework],BLANK()) = "False"))
/
CALCULATE(AVERAGE('ng PoMixParameter'[parameter2Value]),'ng PoMixParameter'[phaseType] = "YBN1", 'ng PoMixParameter'[parameter2Id] = "BaseQuantity"

As you can see what I'm doing is calculating the summatory of kgs (QuantityFP) according to some filters from its table and dividing it by the average of nominal speeds according to some filters as well.

There is a variable that links the two tables, linking the idPO from the table 'ng SC-EGR-EGR-BAT' with the idPO from the table 'ng PoMixParameter', which is the table that stores the nominal speed per each OP. 

I've tried to explain it as well as possible, if any of you have any doubts please let me know and I'll be happy to answer them.

Is there anyone that could help me out with this formula? Any help would be greatly appreciated!

Thanks in advance,

Guerau

[Greg_Deckler]

@GuerauFF I love the operations stuff! Could you please post sample data and expected output. I know it's kind of a pain but it makes the resolution time 10x faster and 100% more accurate. Please @ me in response. I love these kinds of problems! Better yet, the PBIX file posted as a shared link.

Are you getting an error with the formula or just not the right results?

[GuerauFF]

Hi @Greg_Deckler !

Love your enthusiasm with this use-case! The formula is working fine, it's the result that is not coming out right.

Sadly due to compliance issues with my company I'm not allowed to share the original data set but I've created a simpler version that depicts the same issue so you can see what I mean. 

I attach both the pbix file and the excel data set (with the expected result as you said). I think I granted you access correctly, if not let me know and I'll do it again (I could send them to you via e-mail if needed).

Hope you can help me out!

Guerau 

https://drive.google.com/file/d/1LpNhwRC1fRGqi7Oh8dpfYXa5nMf37_Zg/view?usp=sharing 

https://drive.google.com/file/d/1ss8WDZ8kXDOmCITi0SPPX1WauAK75CUS/view?usp=sharing

[Greg_Deckler]

@GuerauFF - OK, if I understand this correctly this wasn't bad at all. See GoodProdTimeAll measure. PBIX is attached below sig.

GoodProdTimeAll = 
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                'Hoja1',
                "__PHM",[PHM]
            ),
            "__GoodProdTime",DIVIDE([Quantity],[__PHM],0)
        )
RETURN
    SUMX(__Table,[__GoodProdTime])

[GuerauFF]

Thanks for the help @Greg_Deckler !

I have one doubt still though... How would you apply the filters that I mentioned? Both the quantity and the nominal speed should be filtered according to certain specifications on some of the other columns of their table. 

The original data set is quite more complex than this example and I'm finding it difficult to apply your solution. 

Really appreciate your help though, would be awesome if we could get this done!

[Greg_Deckler]

@GuerauFF Must have missed the part about filters, will need you to explain that to be please. I have noted where you could apply specific filters although nothing in this measure breaks filter context so you should be able to use it just about anywhere:

GoodProdTimeAll = 
    VAR __Table = 
        ADDCOLUMNS(
            ADDCOLUMNS(
                'Hoja1', //You could use FILTER('Hoja1',<filters go here>)
                "__PHM",[PHM]
            ),
            "__GoodProdTime",DIVIDE([Quantity],[__PHM],0)
        )
RETURN
    SUMX(__Table,[__GoodProdTime]) //You could apply filters here SUMX(FILTER(__Table,<filters>),[GoodProdTime])

[GuerauFF]

I see, thanks for the response.

There is only one thing missing. When I enter the formula you suggested, in the Quantity field it doesn't identify it as a valid variable. This is the formula I am using with my own tables.

GoodProdTimeAll = 
  VAR __Table = 
      ADDCOLUMNS(
         ADDCOLUMNS(
            'Taula de Mesures',
            "__PHM",[PHM-EGR2]
         ),
         "__GoodProdTime",DIVIDE('ng SC_EGR_EGR_BAT'[QuantityFP],[__PHM],0)
      )
RETURN
  SUMX(__Table,[__GoodProdTime])

The part where I do the division it is not allowing me to do that calculus. The variable QuantityFP is formatted as a number so I expected it to work fine, do you know what could be happening? 

I'm not sure what am I missing because it seems like I'm just copying your formula and changing the variables names.