cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Dividing Total Kgs per Process Order according to its Nominal Speed

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):

DubtePowerBI3.JPG

 

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

DubtePowerBI3.1.JPG

 

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

6 REPLIES 6
Highlighted
Super User IV
Super User IV

@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?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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

 

Highlighted

@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])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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!

Highlighted

@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])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors