Forum Discussion

DanielHidalgoS's avatar
DanielHidalgoS
New Member
8 years ago

Accumulative sum by Purchase Order and material - Power Query in Excel

Hi everybody, i need to calculate an acumulative sum by purchase order and material in Power Query for Excel. Like the example...   Status is "ok" with an 85% of the qty ordered has arrived     ...
v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hi DanielHidalgoS,

 

You may try  another option by use Dax in Power BI which should be similar with that in Excel.

 

You could creata one measure for acumulative sum , a calulated column for determin the Status for 85% and one calculated table for your desired.

 

1.measure for acumulative sum

accumlative sum =
CALCULATE (
    SUM ( 'Table1'[Qty arrive] ),
    FILTER (
        ALL ( 'Table1' ),
        'Table1'[Date] <= MAX ( 'Table1'[Date] )
            && 'Table1'[Materia] = MAX ( 'Table1'[Materia] )
            && 'Table1'[Purchase Order] = MAX ( 'Table1'[Purchase Order] )
    )
)

2.a calulated column for determin the Status for 85%

Stauts85% =
IF (
    DIVIDE ( [accumlative sum], 'Table1'[Quantity ordered] ) > 0.85,
    "ok",
    "Pending"
)

3. A calculated Table

Table =
SUMMARIZE (
    'Table1',
    Table1[Purchase Order],
    Table1[Materia],
    "First Date Status 85%", CALCULATE (
        MIN ( 'Table1'[Date] ),
        FILTER ( 'Table1', 'Table1'[Stauts85%] = "ok" )
    )
)

Then you will get the output. You also could have a look at the attatched file.

Hope this also can help you!

 

Best Regards,

Cherry