Log In
Register
Home
May 16, 2020
Offline
Hi,
With reference to the above image. Please help with adding "Best of Network" column using power query.
Let me brief you about what I am actually trying to achieve
I have customer data from different locations and I am trying to fill out the table above using power pivot. For data transformation, I am using Power query.
The words Served, -ve impact, DR% and Sigma level are numerical values which I would be needing in the above mentioned format. I was able to get values for location HYD and Rest of Network (While data transformation in power query - by creating a new column named Rest of Network and using if statements to fill it with "others" if location is not HYD).
But I am unable to figure out how to get the best performing location.
Let's say we have locations 1,2,3,4,5 and location 3 is the best performing one, then I would need Served, -ve impact, DR% and Sigma level values for location 3.
I thought of using the min() function but it was a lot of manual work. Therefore, I am seeking help here.
Can this be done in power query so that I can have everything in a single pivot table
July 16, 2010
Offline
Hi Salman,
Please upload the image again, it didn't work. Note: an Excel sample file would be better than an image, if you can.
Mynda
May 16, 2020
Offline
Hi Mynda,
I have attached an excel sample file with inputs on what exactly I am looking for. Any help would highly be appreciated.
July 16, 2010
Offline
Hi Salman,
Thanks for sharing your file, however I'm not understanding where you got the figures for the 'Best of Network' in column P of the PivotTable. They don't appear to reconcile to the figures in your table, so the example is difficult to understand and work back to the source data.
Also, you're using terms familiar to you, but not to me e.g. DR%. Please explain how you would calculate the Best of Network figures using plain English and with reference to your example data so I can follow the audit trail.
Thanks,
Mynda
May 16, 2020
Offline
Hi Mynda,
Thank you for the reply and apologies for creating that confusion. Kindly ignore the jargons (Served, -ve impact, DR% and Sigma level).
In the sample excel sheet attached, I have used some common terms like product sales and service sales.
Earlier I have just randomly added values to the "Best of network" column (P). However, now I have corrected it and added the actual values using a separate pivot table.
Thank you
July 16, 2010
Offline
Hi Salman,
Thanks for clarifying. There are a couple of issues/questions:
1. You can't do this with a regular PivotTable. You'd need to write a Power Pivot DAX measure. Do you have a version of Excel with Power Pivot?
2. You can't have the layout as you displayed in a PivotTable. i.e. You're showing the 'best of network' values as a column label, but it would need to be a measure and shown in the row labels with the other measures (sum of product sales and sum of service sales).
3. You haven't said whether you'll ever want the Best of Network figure broken down by month, like you have for HYD and Rest of Network.
Mynda
May 16, 2020
Offline
Hi Mynda,
Thank you for the reply
1. Since I usually work with files averaging ~5 million rows, I have the Power Pivot enabled.
2. Earlier, even I thought to use a measure to populate values for best of network using max() function. However, I thought it wouldn't work because the columns HYD and 'Rest of Network' would change the figures for that measure.
3. 'Best of Network' figure was not necessary to be broken down month by month if it appeared as a column, but since you said that layout isn't possible, I would like to learn to break down the 'best of network' column by month as well.
I would again like to emphasize on point #2 - I am still in a state of confusion for adding 'Best of Network' as a measure owing to the already existing columns HYD and Rest of Network
July 16, 2010
Offline
Hi Salman,
Essentially what you want to do is treat this as another location called 'Best of Network'. To do that you'd have to add it to the rows of the table, which would then affect the Grand Total in your PivotTables. I don't think it makes sense to do this.
If you want it broken down by Month then at what point are you deciding which is the Best of Network value? e.g. on a month by month basis, or on a total for the period? If you choose on a month by month basis, then adding up those figures isn't going to give you an accurate total that represents one location because different locations could be the Best of Network each month.
You need to think this through more thoroughly.
In the attached file I've used Power Query to extract the Best of Network Product and Service values and added them as fields to your source data so they can be included in the PivotTable. I think this partially illustrates the issues with what you're wanting to achieve.
Mynda
May 16, 2020
Offline
Hi Mynda,
Thanks a ton for your support on this.
It really helped me expand my horizons with respect to Power Query. Using the approach that you illustrated in the excel sheet (list.max), I was able to create month by month "Best of Network". Totally agree with you on the fact that adding up the figures won't give an accurate value, hence I'll be hiding the grand totals.
1 Guest(s)
