Forum Discussion
percentage
I have my calculations but one month is not working correctly. Jan Feb and Mar should be divided by three - Jan and Feb are correct but March is not. We added some department in April so that is why the divisable number went up to four and then 5 in May. I cannot add another "IF" to pull in a "A3" Var for the divisable by 5 need
March should be 16% - because only one audit was done that was 95% but there should have been 2 so it should be 95 plus 0 divided by 3 to be 16% for the total monthly percent. Jan and Feb are correct and so is April - May is where the divided by 5 will start.
Hi,aghtyson
As I understand it, your requirement is to return A1 results in the first three months, A2 results in April, and A3 results in May.
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2. Below are the measure I've created for your needs:
Measure = VAR P1=CALCULATE([Total %],FILTER(ALLSELECTED(AuditDatebase2),'AuditDatebase2'[Department]="DF1 Zone1 Thru 3")) VAR P2=CALCULATE([Total %],FILTER(ALLSELECTED(AuditDatebase2),'AuditDatebase2'[Department]="DF1 Zone1 Thru 6")) VAR P3=CALCULATE([Total %],FILTER(ALLSELECTED(AuditDatebase2),'AuditDatebase2'[Department]="DF1(Zone 1F1)200-207")) VAR P4=CALCULATE([Total %],FILTER(ALLSELECTED(AuditDatebase2),'AuditDatebase2'[Department]="DF1 Z1-Z3 (800-822)")) VAR P5=CALCULATE([Total %],FILTER(ALLSELECTED(AuditDatebase2),'AuditDatebase2'[Department]="DF1 Z4- Z6 (824-896)")) VAR A1=FORMAT((P1+P2+P3)/3,"0%") VAR A2=FORMAT((P1+P2+P3+P4)/4,"0%") VAR A3=FORMAT((P1+P2+P3+P4+P5)/5,"0%") RETURN IF(MONTH(MAX('tblEvaluationSectionScores'[DateSumbmitted]))<=3,A1, IF(MONTH(MAX('tblEvaluationSectionScores'[DateSumbmitted]))=4,A2, IF(MONTH(MAX('tblEvaluationSectionScores'[DateSumbmitted]))=5,A3)))3.A relationship is established between the tables:
4.Here's my final result, which I hope meets your requirements.
Would it be possible to provide sample data and sample output in tabular format if my results don't meet your needs?Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
I changed my formula from IF(B>1 to IF(B>=1 and it fixed the total percentage across the bottom but it messed up my monthly percent for each department line...Don't know why
Hi,aghtyson
As I understand it, your requirement is to return A1 results in the first three months, A2 results in April, and A3 results in May.
Regarding the issue you raised, my solution is as follows:
1.First I have created the following table and the column names and data are the data you have given:
2. Below are the measure I've created for your needs:
Measure = VAR P1=CALCULATE([Total %],FILTER(ALLSELECTED(AuditDatebase2),'AuditDatebase2'[Department]="DF1 Zone1 Thru 3")) VAR P2=CALCULATE([Total %],FILTER(ALLSELECTED(AuditDatebase2),'AuditDatebase2'[Department]="DF1 Zone1 Thru 6")) VAR P3=CALCULATE([Total %],FILTER(ALLSELECTED(AuditDatebase2),'AuditDatebase2'[Department]="DF1(Zone 1F1)200-207")) VAR P4=CALCULATE([Total %],FILTER(ALLSELECTED(AuditDatebase2),'AuditDatebase2'[Department]="DF1 Z1-Z3 (800-822)")) VAR P5=CALCULATE([Total %],FILTER(ALLSELECTED(AuditDatebase2),'AuditDatebase2'[Department]="DF1 Z4- Z6 (824-896)")) VAR A1=FORMAT((P1+P2+P3)/3,"0%") VAR A2=FORMAT((P1+P2+P3+P4)/4,"0%") VAR A3=FORMAT((P1+P2+P3+P4+P5)/5,"0%") RETURN IF(MONTH(MAX('tblEvaluationSectionScores'[DateSumbmitted]))<=3,A1, IF(MONTH(MAX('tblEvaluationSectionScores'[DateSumbmitted]))=4,A2, IF(MONTH(MAX('tblEvaluationSectionScores'[DateSumbmitted]))=5,A3)))3.A relationship is established between the tables:
4.Here's my final result, which I hope meets your requirements.
Would it be possible to provide sample data and sample output in tabular format if my results don't meet your needs?Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Leroy Lu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I did determine that IF I go into the cell (in the source file that is) and add a .0 before the # it displays correctly as a percentage.
For instance the first line is supposed to be 2.7%: when I change it to Percentage type it displays as 270%. but after I added changed it to .0270 and made it Percentage type it comes up as 2.70%.
This is NOT a feasible resolution because there's over 18,000 Lines on this Spreadsheet & I don't have the time to change every single line fo course!!!I managed to get all my formulas correct and it is doing what I need it to do, however, my issue now is if a zone was to have completed two audits for June and they only completed one audit and the total score of that audit was a 95, it is giving me a 95% score instead of figuring in a "0" score for the other audit. So the score should be 47.5 instead of 95%. What formula do I need to add to my measure to fix this issue? this is my current measure
