cancel
Showing results for
Did you mean:
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.

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

Re: Aggregating based on 2 Groupings

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

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

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

Regular Visitor

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

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

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

Re: Aggregating based on 2 Groupings

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

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

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.

Kind Regards,

Brian.

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.