Hello
I am trying to calculate packs per minute from works order data. The SQL data source records the “time taken” on separate rows to the quantity produced.
For each works order the packs per minute (PPM) would be Output qty / time in minutes. If there were two works orders I want (PPM1 + PPM2) / no of works orders in selection i.e. 2
but I can’t get my head around the DAX. Please note it is only the quantity of the finished good that should be used in the PPM (e.g. items starting with FG)
I have attached the PBIX file with report called “Example of Problem” to demostrate, I would be very grateful if someone could help me with the DAX or direct me to an existing video, many thanke LizzieReports06 vNAV Production minimal.pbix (680.6 KB)
Hi @LizzieTompsett, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
-
Use the forum search to discover if your query has been asked before by another member.
-
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
-
Use the proper category that best describes your topic
-
Provide as much context to a question as possible.
-
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.
Hi @LizzieTompsett
Use this measure…hope this is what you are trying to figure out
Touchscreen Pack Output PPM Updated =
VAr A = ADDCOLUMNS(
Summarize(‘Touchscreen Log Grouped’,‘Touchscreen Log Grouped’[nav Touchscreen Log Entry_Production Order No]),
“DateDiff”,CALCULATE(Sum(‘Touchscreen Log Grouped’[Log Entry DateDiff])),
“Outputqty”,CALCULATE(SUM(‘Touchscreen Log Grouped’[No of Packs]))
)
VAr B = ADDCOLUMNS(A,“Div”,Divide([Outputqty],[DateDiff],0))
VAR C = Sumx(B,[Div])
VAr D = CALCULATE(COUNTROWS( Filters(‘Touchscreen Log Grouped’[nav Touchscreen Log Entry_Production Order No_])),ALLSELECTED(‘Touchscreen Log Grouped’))
Return
DIVIDE(C,D,0)
Regards,
Hemant
Hi Hemant
This works beautifully, it has thrown up a couple more issues through testing but I have managed the source data to overcome these, thank you so much for your super fast response, Lizzie
1 Like
It’s great to know that you are making progress with your query @LizzieTompsett. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Hello Hemant
I have hit another problem whilst testing the measure.
If one or more production order numbers are selected then VAR D counts correctly based on the selection, however, if “Select All” is used in the slicer visual then VAR D totals 19, 249 (i.e. all prod orders) which makes the measure result look like nonsense.
On the dashboard there are other slicers for “Shift date” and “Event Recorded by” and when these are used the total number or prod orders shouldn’t be 19,249 but 55.
This there a way that the PPM measure can recognise the selection in the Shift Date and Recorded by slicers even if the Production order no slicer is set to “Select All”?
I have attached the pbix file again there are two reports showing the different prod order no slicers and the effect it has on VAR D and the full PPM measure, very many thanks L
Reports06 vNAV Production YX.pbix (2.9 MB)
/use This then…
VAR D =
/* CALCULATE(COUNTROWS(
FILTERS(‘Touchscreen Log Grouped’[nav Touchscreen Log Entry_Production Order No_])),
ALLSELECTED(‘Touchscreen Log Grouped’[nav Touchscreen Log Entry_Production Order No_]))
*/
VAR A =
Summarize(‘Touchscreen Log Grouped’,‘Touchscreen Log Grouped’[nav Touchscreen Log Entry_Production Order No_])
Return
COUNTROWS(A)
In this scenario you need to understand the context in which you want to do calculation.
Perfect - thank you again cannot tell you how much this has helped.
Very best regards Lizzie
Thanks - please can you tell me how to mark my thread as solved…? Thanks L
Hi @LizzieTompsett. Click on the ellipsis in the bottom-right corner of the post that solved your query, then click on the Solution link … it should change from grey to green.
Dear Hemant
Would you be kind enough to help me again - I had the production metrics problem that you helped me with in July. The client now wants to measure the metric against a list of targets.
I have adapted the solution you supplied before so that I get the right result for a single works order but I don’t know how to get the result to work for multiple orders.
I have attached the pbix file - on this there is a tab called Workings - Enterprise DNA where I have shown all my workings and extra commentary about what I am trying to achieve.
Very many thanks for any help you can give, Lizzie
You might want to start a new topic message, so someone will be able to help you quickly as this message string has been marked that was solution has been applied
thanks
Keith
Thanks Keith - the problem is now all sorted
1 Like