Forum Discussion
Average across multiple data sources
Hi All,
I have 3 data sources that are pulled from our Survey tool (3 different customer surveys). Each of the 3 surveys has a field for NPS (Net Promoter Score). Ultimately, I would like to average all three NPS columns by month to show the monthly NPS score across all the surveys. Is there a way I can do this? Keep in mind the following;
The raw data is coming from 3 separate excel sheets (I don't think the survey tool allows a direct connect for data capture.
All three files have a column named NPS which is numerical
All three files have a date column that captures the date the survey was completed.
TIA
Mike
Hi Anonymous
From the data you provided , I got the same result as you want .
(1)Create a column to return the month number of Date .
Month number = MONTH('Table'[Date])(2) Group by Month number and average .
average = CALCULATE(AVERAGE('Table'[NPS]),ALLEXCEPT('Table','Table'[Month number]))I doubt that you said but they don't look right . Why do you say that? So what should be the correct result ?
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous few questions:
- 3 files, are you combining those into one or keeping it separate?
- you want 3 avg lines, for each file?
- do you want to see avg over time (I guess)?
- do you have a date dimension in your model that has relationship to these 3 tables, if not add one from my post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi Parry,
To answer your questions:
1. I need to keep them as separate files because each has different questions depending on the survey filled out. The only similar columns is the date and the NPS.
2. I want to AVG all 3 together as one number.
3. Yes this would be the end goal to show a monthly combined AVG of the 3.
4. Yes I tried that, but I couldn't figure out how to get the base AVG of the 3 first.
Thanks
Thanks again. We're close. I am able to get the values, but they don't look right. I only have 2 months of data so it is small to start. For July I get a total average of 8.5 (this one is off) and August 7.67 (this one looks ok). I've include the actual data so you can see this. Based on my calculations, July should be 7.33.
Date Survey NPS 8/31/2021 TAC 5 8/28/2021 TAC 8 8/23/2021 TAC 2 8/22/2021 TAC 10 8/20/2021 TAC 6 8/18/2021 TAC 10 8/11/2021 TAC 10 7/29/2021 TAC 7 7/29/2021 TAC 10 7/21/2021 TAC 7 7/15/2021 TAC 1 7/13/2021 TAC 10 7/12/2021 TAC 9 7/8/2021 TAC 5 7/7/2021 TAC 7 7/1/2021 SA 10 8/29/2021 SA 10 8/12/2021 Field 8
Anonymous try this, add a date table, link to 3 files, it will be one to many relationship and then add this measure:
Avg = AVERAGEX ( DateTable, CALCULATE ( SUM ( Table1[NPSColumn] ) ) + CALCULATE ( SUM ( Table2[NPSColumn] ) ) + CALCULATE ( SUM ( Table3[NPSColumn] ) ) )to visualize, use the date or any other column from the calendar table and above measure.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi Anonymous
From the data you provided , I got the same result as you want .
(1)Create a column to return the month number of Date .
Month number = MONTH('Table'[Date])(2) Group by Month number and average .
average = CALCULATE(AVERAGE('Table'[NPS]),ALLEXCEPT('Table','Table'[Month number]))I doubt that you said but they don't look right . Why do you say that? So what should be the correct result ?
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
