RE: Difference of average scores from current month to previous month.

[Fidezuri1990]

Hi PBI family,

I am trying to calculate the difference of average score between current month and previous month.

I have a set of data (Table name: "Key Metrics Data") as shown below.

The average score across Jul, Aug & Sep is 81.7% [(100%+90%+55%)/3]. The average score across Jul & Aug is 95%[(100%+90%)/2]. The difference between the two averages is 81.7% - 95% = -13.3%. That is a drop of 14% from Aug Avg score (95%) to Sep Avg Score (81.7%). Of cse, I am trying to automate this by developing a dax formula as shown below:

However, the result shown 'PercentageDifference" is 0.0% instead of -14%.  I checked that my 'AvgPreviousMth' score is the same as 'AvgSelectedMth' (81.7%). That is why I obtained 0.0%.  The 'AvgPreviousMth' should be 95% as it should not include the Sep score of 55%. 

KPI showing 0.0%.

Below is my Calendar Table called 'CalendarDax'.

Could someone please help what is should be rectify in the formula so that 'AvgPreviousMth' score will take the average all the months less the current month (or the selected month)? As example, if I choose Nov-24, 'AvgPreviousMth' score will include all months up to Oct-24. The objective is to display the monthly average score drop/increase for my KPI. Thank you. 

[v-fenling-msft · Accepted solution]

Hi, @Fidezuri1990 

May I ask if this table Key Metrics Data was originally available to you? 

 

If so, do you mean that your Final Assessed APS is already made into a table by month? 

If so, you can refer to my steps to calculate the average score: 

First create a Measure to calculate the average score for each month: 

M_1 = 
VAR _date =
    MAX ( 'Key Metrics Data'[Date] )
VAR _sum =
    CALCULATE (
        SUM ( 'Key Metrics Data'[Final Assessed APS] ),
        FILTER ( ALL ( 'Key Metrics Data' ), 'Key Metrics Data'[Date] <= _date )
    )
VAR _sumrows =
    CALCULATE (
        COUNTROWS ( 'Key Metrics Data' ),
        FILTER ( ALL ( 'Key Metrics Data' ), 'Key Metrics Data'[Date] <= _date )
    )
RETURN
    DIVIDE ( _sum, _sumrows )

Then create another Measure to calculate the difference of the average for each month: 

M_2 = 
VAR num = [M_1]
VAR earilerNum =
    CALCULATE (
        [M_1],
        FILTER (
            ALL ( 'Key Metrics Data' ),
            'Key Metrics Data'[Date] < MAX ( 'Key Metrics Data'[Date] )
        )
    )
RETURN
    earilerNum - num

Here is the test result: 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-fenling-msft · Accepted solution]

Hi, @Fidezuri1990 

May I ask if this table Key Metrics Data was originally available to you? 

 

If so, do you mean that your Final Assessed APS is already made into a table by month? 

If so, you can refer to my steps to calculate the average score: 

First create a Measure to calculate the average score for each month: 

M_1 = 
VAR _date =
    MAX ( 'Key Metrics Data'[Date] )
VAR _sum =
    CALCULATE (
        SUM ( 'Key Metrics Data'[Final Assessed APS] ),
        FILTER ( ALL ( 'Key Metrics Data' ), 'Key Metrics Data'[Date] <= _date )
    )
VAR _sumrows =
    CALCULATE (
        COUNTROWS ( 'Key Metrics Data' ),
        FILTER ( ALL ( 'Key Metrics Data' ), 'Key Metrics Data'[Date] <= _date )
    )
RETURN
    DIVIDE ( _sum, _sumrows )

Then create another Measure to calculate the difference of the average for each month: 

M_2 = 
VAR num = [M_1]
VAR earilerNum =
    CALCULATE (
        [M_1],
        FILTER (
            ALL ( 'Key Metrics Data' ),
            'Key Metrics Data'[Date] < MAX ( 'Key Metrics Data'[Date] )
        )
    )
RETURN
    earilerNum - num

Here is the test result: 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fidezuri1990 · Accepted solution]

hi @v-fenling-msft ,

Sorry for the late reply. To answer your question on 

"May I ask if this table Key Metrics Data was originally available to you? 

If so, do you mean that your Final Assessed APS is already made into a table by month?"

Yes, the data is already made into the table every month. That is raw data directly from Excel.

The solution you proposed works perfectly fine and thank you so much. Greatly appreciate it. I was trying on it for 2-3 days and get to nowhere. Thank you once again and being a great contributor to this community. Cheers