cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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!

Guerau

6 REPLIES 6
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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

Super User

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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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!

Super User

@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 =
'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])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors