Forum Discussion

neil37's avatar
neil37
Advocate I
4 years ago
Solved

Calculated Column or Measure Question

Hello, 

 

I believe I am on the way to solving this but need that extra push from the great forum that this is. 

 

If I have a dataset that resembles the below calculated table named "cTbl_TotalScoreCalculate"

 

PersonTotaled Score
Jim123
Mike234
Greg345
Jessica64

 

And I wanted to create either a measure or a calculated column that executed this function:

=(A1 - MIN(A:A)) / (MAX(A:A) - MIN(A:A))

 

What would I add in the syntax for what would be the bold red variable below?

 

 I have the following syntax created but I am strugling with getting the syntax to calculate:

ScaledValue =
VAR MaxScore = MAX(cTbl_TotalScoreCalculate[Totaled Score])
VAR MinScore = MIN(cTbl_TotalScoreCalculate[Totaled Score])
Return
CALCULATE(((A1)-MinScore)/ (MaxScore - MinScore))

Caveats: The table where this calculated column is being generated is a calculated Table. 

 

Thank you for your assistance!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a calculated measure.

     

     

    Expected result: =
    VAR _min =
    CALCULATE ( MIN ( cTbl_TotalScoreCalculate[Totaled Score] ), REMOVEFILTERS () )
    VAR _max =
    CALCULATE ( MAX ( cTbl_TotalScoreCalculate[Totaled Score] ), REMOVEFILTERS () )
    RETURN
    IF (
    HASONEVALUE ( cTbl_TotalScoreCalculate[Person] ),
    DIVIDE ( SUM ( cTbl_TotalScoreCalculate[Totaled Score] ) - _min, _max - _min )
    )

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a calculated measure.

     

     

    Expected result: =
    VAR _min =
    CALCULATE ( MIN ( cTbl_TotalScoreCalculate[Totaled Score] ), REMOVEFILTERS () )
    VAR _max =
    CALCULATE ( MAX ( cTbl_TotalScoreCalculate[Totaled Score] ), REMOVEFILTERS () )
    RETURN
    IF (
    HASONEVALUE ( cTbl_TotalScoreCalculate[Person] ),
    DIVIDE ( SUM ( cTbl_TotalScoreCalculate[Totaled Score] ) - _min, _max - _min )
    )
  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi neil37 

     

    Try this to add a new column with DAX:

     

    ScaledValue =
    VAR MaxScore = calculate(MAX(cTbl_TotalScoreCalculate[Totaled Score]),all(cTbl_TotalScoreCalculate))
    VAR MinScore = calculate(MIN(cTbl_TotalScoreCalculate[Totaled Score]),all(cTbl_TotalScoreCalculate))
    Var _A1 = EARLIER ( cTbl_TotalScoreCalculate[Totaled Score] )

    Return
    (_A1-MinScore)/ (MaxScore - MinScore)

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/