Forum Discussion

ChrisWeeks92's avatar
ChrisWeeks92
Icon for Helper I rankHelper I
8 years ago

DAX Measure to acheive target KPI

The company i work for have a client which gets audited on 5 different sites every day. The client has a KPI of 95% for each site which they need to acheive at month end. If they do not acheive the target KPI then that could cost the company i work for.  I know they should be aiming for 100% each day but we want to show what the score is to get to the 95% KPI.   

 

Is there a "Goal-Seek" formula/function within Power BI that will calculate the average score of the month so far then work out what score they need in the next audit to bring them back to the 95% KPI?

 

For example say by day 15 of the month they have acheived 85% as their average score that month. What score would they need to reach that 95% target? and for example the outcome of the formula might be 97% in their next audit to get the 95% average for the  month. 

 

My data is sorted in the below format;

Audit Number | Site | Contract| Username| Note| Total Score| Date/Time|

 

I have been racking my brains about this for ages now and cannot seem to work out what formula I need to use so any help would be greatly appreciated.  

 

Need Help

5 Replies

    • ChrisWeeks92's avatar
      ChrisWeeks92
      Icon for Helper I rankHelper I
      8 years ago

      hi Anonymous

       

      Apologies, sample data would be on my google drive:  Demo Data

       

      Hopefully it all makes sense,  basically i need a formula/way to find out what score is needed to acheive the KPI score for each site.  The KPI for the site is 95%. The site needs to reach 95% average by the end of the month.  

       

      Essentially i need a Card visual showing the score needed to acheive the 95% KPI target?

       

      Any help is greatly appreciated.  

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi ChrisWeeks92,

         

        You can try to use below formula measure to get rolling average group by year month.

        Rolling AVG =
VAR currDate =
    MAX ( Sheet1[Date/Time] )
RETURN
    CALCULATE (
        AVERAGE ( Sheet1[TotalScore] ),
        FILTER (
            ALLSELECTED ( Sheet1 ),
            Sheet1[Date/Time] <= currDate
                && FORMAT ( [Date/Time], "mm/yyyy" ) = FORMAT ( currDate, "mm/yyyy" )
        ),
        VALUES ( Sheet1[Username] ),
        VALUES ( Sheet1[Site] )
    )

         

        Card visual not suitable for your requirement, please use multi-row card to instead.

         

        Regards,

        Xiaoxin Sheng

  • Spurta's avatar
    Spurta
    Frequent Visitor
    8 years ago

     

    Daily Average to Quota =
IF (
    CALCULATE ( SUM ( Quotas[Quota] ) )
        - CALCULATE ( SUM ( 'Sales Invoiced'[Extended Amount] ) )
        > 0,
    (
        CALCULATE ( SUM ( Quotas[Quota] ) )
            - CALCULATE ( SUM ( 'Sales Invoiced'[Extended Amount] ) )
    )
        / [Selling Days left in Month],
    0
)

     

     

    Not sure if you already had this issue answered, but if it helps... I've set up something similar with reporting on a Sales vs Quota report, showing what daily average is needed for the remainder of the month to hit Quota. If you replace Quota with KPI and sales with audit scores, I'd imagine it's the same concept.

     

    Using the above (along with various other formulas) I've been able to create metrics like the following:

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Spurta  

      Hi, The metric looks amazing! Would you be able to show us how you made that projected sales gauge? There is a projected sales you can use on line graphs, but I would like to use it on other visuals.

       

      Thanks!