Forum Discussion
Solved
Monetary unit sampling - Power Query
Hello community,
I would very much appreciate any help. I'm trying to create dynamic MUS report (monetary unit sampling). I need to filter each line in Excel (in attachment as Source table) that exceeds certain amount (Criteria) of accumulated value (in example Accumulation) and create a report only with this lines (in attachment as Report table). Is there a way to do it? So I need to filter all lines for which accumulation exceeds 150 in my case.
ratishkp You can assign this to a shape or button:
Sub foo()
ActiveWorkbook.RefreshAll
End Sub
Or if the PivotTable is on another sheet, you can add ActiveWorbook.RefreshAll to the Worksheet_Activate event, and it will automatically update whenever the sheet is selected.
4 Replies
Hello,
I have a similar problem, how to do a MUS report (monetary unit sampling) in power query.
however, I have to add a random variable.
therefore I would like to prepare the report in a different way. Below I present how I did it in EXCEL.
please help me translate this into POWER QUERY.thank you in advance
Formulas in EXCEL (sorry that I didn't paste image, but I don't know how to do it on this blog)
index item from to chosen to sample random variable 2 1 3 =J3 ( random variable) =E4 (from)+D4 (item)-1 =AND($J$4>=E4;$J$4<=F4) Sampling Interwal 5 2 2 =IF(G4;1;F4+1)
(if above sample is chosen then start from 1 else start from previous (to))
=E5+D5-1 =AND($J$4>=E5;$J$4<=F5) 3 4 =IF(G5;1;F5+1) =E6+D6-1 =AND($J$4>=E6;$J$4<=F6) 4 1 =IF(G6;1;F6+1) =E7+D7-1 =AND($J$4>=E7;$J$4<=F7) 5 4 etc etc etc 6 1 7 2 8 5 and below is result in excel
index item from to chosen to sample random variable 2 1 3 2 4 FALSE Sampling Interwal 5 2 2 5 6 TRUE 3 4 1 4 FALSE 4 1 5 5 TRUE 5 4 1 4 FALSE 6 1 5 5 TRUE 7 2 1 2 FALSE 8 5 3 7 TRUE Unfortunately i can't help you with this. Maybe you want to start a new discussion. There are several experts who might be able to help you with a Power Query solution. And could you add the column names to your tables like in the example below:
column E column F column G column H column I column J column K column L index item from to chosen to sample random variable 2 1 3 2 4 FALSE Sampling Interwal 5 2 2 5 6 TRUE 3 4 1 4 FALSE 4 1 5 5 TRUE 5 4 1 4 FALSE 6 1 5 5 TRUE 7 2 1 2 FALSE 8 5 3 7 TRUE
- Thank you very much! Will try on my database.
