Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Brianoreilly
Helper II
Helper II

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
v-qiuyu-msft
Community Support
Community Support

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.

View solution in original post

7 REPLIES 7
dhusanth
Frequent Visitor

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.

v-qiuyu-msft
Community Support
Community Support

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.

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
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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
Helper II
Helper II

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.