Aggregating based on 2 Groupings

[Brianoreilly]

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

[dhusanth]

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.

[v-qiuyu-msft · Accepted solution]

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 

[Brianoreilly]

Hi 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. 

[TomMartens]

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

[Brianoreilly]

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.

[Brianoreilly]

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

[Brianoreilly]

Anybody with any idea....... please .....