Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Powerbi PPM calculation

Hi Powerbi Community,

I am struggeling with a PPM calculation I am trying to preform.

 

All files do have relationship between them.


I do have the total quantity supplied to several customers in "Main Data file"

Main file:

 

Customer ID:
Customer name:
Customer Location: Do not want to use
Customer supplied quantities:

Supplied part:

Supporting file: 

Customer ID:
Customer name:
Customer Location: Do want to use this location from this file.
Reported Quantities:
Supplied part:

Currently my formula is like "Taken from another formula on powerbi community"


PPM = CALCULATE(SUM('Complaint Tracking'[Quantity Reported]),ALLSELECTED('Complaint Tracking'[Customer Location]))
/
CALCULATE(SUM('0112017_CQE_Data'[Net Inv QTY]),ALLSELECTED('0112017_CQE_Data'))*1000000

For me it seems the PPM calculation is off and incorrect.

I want to see the PPM per.

Customer: Shows globally the whole customer PPM for all parts.
Customer Location: Shows globally all the PPMs per location.
Part supplied PPM globally: PPM globally per part family.

Do these need to be seperate PPM calculation ?

Hopefully someone can give me some support.

Thank you,

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    Anonymous,

    Change formula of PPM to the following , then create visuals combining different fields with this measure and check if the measure returns your expected result.

    PPM = CALCULATE(SUM('Supporting Data'[Quantity Reported]))/CALCULATE(SUM('Main Data'[Net Inv QTY]),ALLSELECTED('Main Data'))*1000000



    Regards,
    Lydia

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous,

    Change formula of PPM to the following , then create visuals combining different fields with this measure and check if the measure returns your expected result.

    PPM = CALCULATE(SUM('Supporting Data'[Quantity Reported]))/CALCULATE(SUM('Main Data'[Net Inv QTY]),ALLSELECTED('Main Data'))*1000000



    Regards,
    Lydia

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous,

    Could you please share dummy data of your tables and post the expected result in table format?

    Regards,
    Lydia

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    So hereby I added some picture trying to explain it better.

    Currently I am looking for ppm calculation which shows me the correct data.

    Main file provides me with the customer name + Quantity shipped that particulair YTD.

    Supporting file provide me with the Quantity reported " NOK / Not good material" YTD + Location which reported + Customer name.

    The stack charts I want to show on my Powerbi.Main FileSupporting FileCharts I would like to represent, Customer PPM globally - PPM per location and PPM per family product.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Anonymous,

      Could you please store the dummy data of your tables in Excel file and share me the Excel file via OneDrive? Also please help to post expected result in table format based on the data you share to me.

      Regards,
      Lydia

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Lydia,

        Sorry for late reply.

        Dummy Data hereby the link to the dummy data.

        I didn't get the part you wanted me to put the expected results in table format ? Do you have an example.

        Thank you,