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:
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,
@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?
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!
@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])
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!
@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])
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.
VAR __Table =
'Taula de Mesures',
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.
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.