cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MrMosselMan Frequent Visitor
Frequent Visitor

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Powerbi PPM calculation

@MrMosselMan,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Moderator v-yuezhe-msft
Moderator

Re: Powerbi PPM calculation

@MrMosselMan,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
MrMosselMan Frequent Visitor
Frequent Visitor

Re: Powerbi PPM calculation

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.MainFile.pngMain FileCQE_CC.pngSupporting FileTable_Charts.pngCharts I would like to represent, Customer PPM globally - PPM per location and PPM per family product.

Moderator v-yuezhe-msft
Moderator

Re: Powerbi PPM calculation

@MrMosselMan,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MrMosselMan Frequent Visitor
Frequent Visitor

Re: Powerbi PPM calculation

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,

Moderator v-yuezhe-msft
Moderator

Re: Powerbi PPM calculation

@MrMosselMan

I am not able to access the above file. I want to know your expected result based on the dummy data, it is easy for us to understand if you put the expected result in table or cells.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MrMosselMan Frequent Visitor
Frequent Visitor

Re: Powerbi PPM calculation

Dropbox Link

Hi Lydia,

OneDrive I cannot share the data as it has been disabled.

I do understand you want it in table format. But I do not know the formula to get that typ of result for excel, I have to dive in to it.

So you are saying you do not know where I want to go with the explanation.

I want a Parts per million calculation.

Per family "Part name"
Per customer total.
Per customer Location.

Current formula in Powerbi is use = "Doesn't show the correct data", shows for customer location all the same PPM.

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

Second question would be, do I need several PPM calculations to do the 3 above cases or can I do it with 1 formula.

Moderator v-yuezhe-msft
Moderator

Re: Powerbi PPM calculation

@MrMosselMan,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 247 members 2,173 guests
Please welcome our newest community members: