Forum Discussion
Aggregating based on 2 Groupings
Hi All,
I have a scenario where I need to calculate the "% of Projects that meet our Customer Satisfaction Score" of greater than or equal to 5 and show the result in a Card Visual. This needs to be done on a monthly basis.
The lowest granular level of information is Date & Respondent.
As you can have multiple Respondents & Dates per each Project, I first need to calculate the Average Score Per Each Project Per Each Month.
For instance "Acme Project" average = (7+4)/2 = 5.5 =Meets Satisfaction Score in January.
I then need to formulate:
CountIF( (Average of Project)>=5))
Count of All Projects
Is there an easy way to do this.
I have tried SumX and Summarize functions but to no avail.
I also attempted to create a Projects and Date Table and do a lookup.
Would really appreciate any help, as the month end is approaching and will need for next Monday.
Thanks,
Brian
Hi Brianoreilly,
You can create new calculated column below:
Date(M-Y) = FORMAT('Table1'[Date],"MMM") & "-" & YEAR('Table1'[Date])
Then create a measure to return project satisfaction:
Project Satisfaction = var temp=SUMMARIZE('Table1','Table1'[Date(M-Y)],'Table1'[Project],"Ave",AVERAGE('Table1'[Satisfaction Score]))
return
DIVIDE(COUNTX(FILTER(temp,[Ave]>=5),[Ave]),COUNTROWS(temp))Best Regards,
Qiuyun Yu
7 Replies
Hi All,
I have a scenario where I need to calculate the "% of Projects that meet our Customer Satisfaction Score" >= to 5 and show the result in a Card Visual. This needs to be done on a monthly basis.
The lowest granular level of information is Date & Respondent.
As you can have multiple Respondents & Dates per each Project, I first need to calculate the Average Score Per Each Project Per Each Month.
For instance "Acme Project" average = (7+4)/2 = 5.5 =Meets Satisfaction Score in January.
The lowest granular level of information is Date & Respondent.
As you can have multiple Respondents & Dates per each Project, I first need to calculate the Average Score Per Each Project Per Each Month.
For instance "Acme Project" average = (7+4)/2 = 5.5 =Meets Satisfaction Score in January.
I then need to formulate:
CountIF( (Average of Project)>=5))
Count of All Projects
Is there an easy way to do this.
I have tried SumX and Summarize functions but to no avail.
I also attempted to create a Projects and Date Table and do a lookup.
Would really appreciate any help, as the month end is approaching and will need for next Monday.
Thanks,
Brian
Anybody with any idea....... please .....
Hey,
please provide the data in an easy to reproduce format by preparing a pbix file upload the to onedrive or dropbox, and share the link here.
Regards
Tom
Hi Tom,
Thank you so much for the assistance.
I really appreciate it.
The solution provided by the moderator actually worked and meets my needs.
Thanks again,
Regards,
Brian.
Hi Brianoreilly,
You can create new calculated column below:
Date(M-Y) = FORMAT('Table1'[Date],"MMM") & "-" & YEAR('Table1'[Date])
Then create a measure to return project satisfaction:
Project Satisfaction = var temp=SUMMARIZE('Table1','Table1'[Date(M-Y)],'Table1'[Project],"Ave",AVERAGE('Table1'[Satisfaction Score]))
return
DIVIDE(COUNTX(FILTER(temp,[Ave]>=5),[Ave]),COUNTROWS(temp))Best Regards,
Qiuyun YuHi Qiuyun Yu ,
Thank you so much for the solution.
There was no way in hell, I would of figured that out by myself.
Appreciate your diligent work.
Kind Regards,
Brian.
Hi,
I have an instance where i have to use Multiple Lines in secondary axis.
I am expecting to draw a chart like below in power bi, but i could find option. In the line and clustered column chart, i am able to get only one line in secondary axis.
Please help me on this.
