Forum Discussion

kennylch's avatar
kennylch
Helper I
2 years ago

new column based on measure value

Below is the sample data extracted from my matrix table. 

 

The measure column created using this. it gives the correct value as expected as well as conditional formatting with right colour shade. 

Measure = (sum(KFACTOR[Net Qty])-[Req Qty])/[Req Qty]

 

I try to create a column base on the Measure value. 

Healthy Level = if([Measure]<0,"Unhealthy Level",IF([Measure]<5,"Healthy Level","Above Healthy"))

 

There isnt any issue if PART column not repeating. Eg 'PART 201371605. The measure gives 2 different value (which is correct). But the heathy level column gives incorrect result as it only based on the measure value in a one of the record and apply the same to both row.

 

How to i create the Heathy Level column correctly to display the correct result 

 

 

 

Need Help

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi kennylch - you can create a measure instead of calculated column 

    ex: as per data

    Healthy Level =
    IF(
    [Measure] < 0,
    "Unhealthy Level",
    IF(
    [Measure] < 5,
    "Healthy Level",
    "Above Healthy"
    )
    )

    Hope it helps

    • kennylch's avatar
      kennylch
      Helper I
      2 years ago
      Hi. It doesnt help as this invole 2 table.. Once i insert a measure like this. My other measure became blank
      • rajendraongole1's avatar
        rajendraongole1
        Super User
        2 years ago

        Hi kennylch - you can create measure logic in calculated column, using related function to the data.

        Measure_Column =
        ([Net Qty] - RELATED(OtherTable[Req Qty])) / RELATED(OtherTable[Req Qty])

         

        and then use the create calculated column provided as below:

        Healthy Level =
        IF(
        [Measure_Column] < 0,
        "Unhealthy Level",
        IF(
        [Measure_Column] < 5,
        "Healthy Level",
        "Above Healthy"
        )
        )

         

         

        hope it helps