cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brianoreilly Regular Visitor
Regular Visitor

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. 

 

Project Satisfaction Screenshot.JPG

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Aggregating based on 2 Groupings

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))

 

q4.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Highlighted
Brianoreilly Regular Visitor
Regular Visitor

DAX help: Calculate on a different Level of Detail like in Tableau

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. Project Satisfaction Screenshot.JPG

 

 

 

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 Regular Visitor
Regular Visitor

Re: DAX help: Calculate on a different Level of Detail like in Tableau

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

Super User
Super User

Re: Aggregating based on 2 Groupings

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Moderator v-qiuyu-msft
Moderator

Re: Aggregating based on 2 Groupings

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))

 

q4.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Brianoreilly Regular Visitor
Regular Visitor

Re: Aggregating based on 2 Groupings

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 Regular Visitor
Regular Visitor

Re: Aggregating based on 2 Groupings

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. 

dhusanth Frequent Visitor
Frequent Visitor

Re: Aggregating based on 2 Groupings

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.

 

Capture.JPGPlease help me on this.