Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am attempting to calculate a value called _QPPM using two different sources. The value is derived from the number of identified failure divided by the number of shipments for the month multiplied by one million. The two sources are unrelated. I have set up a simple table. I have added a measure to my query source to capture the value. The failures are captured in one source ‘NCMR’[Initial Qty Reject], and shipping quantity is captured in another source ‘zMerps_Will_ShipInfo’[SHL_ShipQty]. When I attempt to parse the information by month the calculation is incorrect. The sum of all shipments for the year is used in the calculation rather than the sum of shipments by month. Any assistance the community can provide is appreciated.
_QPPM = (SUM(NCMR[Initial Qty Reject]) / SUM('zMERPS_Will_ShipInfo'[SHL_ShipQty])) * 1000000
Solved! Go to Solution.
if possible, can you put sample data form both tables in a excel sheet and send it over, i will put together somethign quickly and share pbix file wiht you, it will be much easier.
I don't see there is any dedicated calendar table and that seems to be first missing part in all this.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
i believe you have dedicated calendar table? if yes, how your tables are related with calendar table?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
The Issue Date slicer corresponds to the table data. This may be part of the issue. The date field in the zMerps table corresponding to the shipping quantity is SH_ShipDate. The data table visual only reflects information from the NCMR query accept for the _QPPM measurement which pulls shipment quantity from the zMerps query. Below, I've provided a snapshot of my values and filters sidebar for the NCMR table.
NCMR Query Fields
zMerps
if possible, can you put sample data form both tables in a excel sheet and send it over, i will put together somethign quickly and share pbix file wiht you, it will be much easier.
I don't see there is any dedicated calendar table and that seems to be first missing part in all this.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
It looks like you are correct. I needed the dedicated calendar; however, the sum value for RejectQty is incorrect. For example, using my data the number of failures for the month of September 2016 should be 363. The number of failures shown is 97.
The calculation used is as follows:
_RejectQty = SUM(NCMR[Initial Qty Reject])
My visualization of the data:
It looks like there is a similar issue for shipped qty. There should be a shipped quantity every month. I have months where there is no shipment quantity.
hey WIll, is it possible for you to share the data, just shoot an excel file and will take a look. I believe you are using PBIX file which I sent to you
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Can you share row level data for Sep 2016? Do you see sum of other months is correct?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
The sum for all months is incorrect. I've sent you a link to the data for Sept. 2016.
Thanks
hey Will, can you send the data int he same format which you sent me yesterday, your new sheet has new columns, if data is in same format, it will be quick for me to take a look what is going on.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I've used the same report export from the database. I've just confirmed the columns are the same as the previous spreadsheet. Can you identify the new columns? I may have overlooked something.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |