Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Strategic Value

Hi everyone, I'm trying to calculate a strategic value, but I'm not sure how to do that in DAX. Below is logic and they come from one single table. Can you please help me have this in DAX?

Strategic Value Planned maintenance PV 30 years =
[((Planned maintenance PV 30 years – the lowest value of “Planned maintenance PV 30 years” field in the whole data set)) / ((Highest value of “Planned maintenance PV 30 years” field in the whole data set) – (Lowest value of “Planned maintenance PV 30 years” field in the whole data set))] * 100
 
Many thanks!
DAX help needed
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hey, amitchandak thanks for your help. 
    I applied the below calculated column and it worked. 

    Planned Maintenance Strategic Value =
    var MAX_Value= MAX(Costs[Planned maintenance PV 30 years])
    var Min_Value = MIN(Costs[Planned maintenance PV 30 years])
    return 100-((Costs[Planned maintenance PV 30 years]-Min_Value)/(MAX_Value-Min_Value))*100

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous ,

     

    M1 =

    divide(

    sum(Table[Planned maintenance]) -Min(Table[Planned maintenance]),

    max(Table[Planned maintenance]) -Min(Table[Planned maintenance])

    )

     

    If needed

    M2 = calculate([M1], all())

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hey, amitchandak thanks for your help. 
      I applied the below calculated column and it worked. 

      Planned Maintenance Strategic Value =
      var MAX_Value= MAX(Costs[Planned maintenance PV 30 years])
      var Min_Value = MIN(Costs[Planned maintenance PV 30 years])
      return 100-((Costs[Planned maintenance PV 30 years]-Min_Value)/(MAX_Value-Min_Value))*100
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much for your reply amitchandak 
      My Min and MAX(Table[Planned maintenance]) aren't giving the correct number. For MIN I get 0, and if I try this measure right here --> 

      CALCULATE( MIN(Costs[Planned maintenance PV 30 years]), FILTER(Costs, Costs[Planned maintenance PV 30 years] <> 0 || Costs[Planned maintenance PV 30 years] <> BLANK())) I get a wrong number also. 

      Below is the screenshot of what I have on excel, and when I click on the column the values for MIN/MAX a the bottom of the sheet are very different from my numbers in PBI.

       


      Any help will be much appreciated. 
      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Anonymous ,


        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi, amitchandak  I've had some progress. 
      The M1 calculation above worked as a total (see 1st picture), but when I add the UPRN, I lose the values (2nd picture) How could I have these strategic values, but UPRN?  
      Many thanks! 


      Below are the measures I used.

      Strategic Value SAP Rating =
      divide(

      sum(Costs[SAP Rating]) -[Min SAP Rating],

      [Max SAP Rating] - [Min SAP Rating]
      )

       

      Strategic Value Planned maintenance PV 30 years =
      divide(

      sum(Costs[Planned maintenance PV 30 years]) -[Min Planned Maintenance PV 30 years],

      [Max Planned Maintenance PV 30 years] - [Min Planned Maintenance PV 30 years]
      )

      Strategic Value Net Surplus =
      divide(

      sum(Costs[Net Surplus Generated from Sale]) -[Min Net Surplus Generated from Sale],

      [Max Net Surplus Generated from Sale] - [Min Net Surplus Generated from Sale]
      )

      Strategic Value = Costs[Strategic Value SAP Rating]+Costs[Strategic Value Planned maintenance PV 30 years]+Costs[Strategic Value Net Surplus]/3
      • Icey's avatar
        Icey
        Community Support
        4 years ago

        Hi Anonymous ,

         

         

         

        Which table does the "UPRN" come from? What's your model structure?

         

        Could you create a sample .pbix file for test? Please don't contain any sensitive information.

         

        Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.