Forum Discussion

Vitorino's avatar
Vitorino
Regular Visitor
3 years ago

Normalization of column to a Measure

Hello community,

I have a dataset of used cars and I want to normalize the car prices according to their brand and model. However, when I create a calculated column, it doesn't apply the selected filters that I choose.

I have tried to create a measure, but unfortunately, I haven't had any success. Here's my DAX code:



Preço Normalizado =
VAR AVGPreco =
    AVERAGEX(
        FILTER(
            'FACT AUTOMOVEIS',
            'FACT AUTOMOVEIS'[Marca] = EARLIER('FACT AUTOMOVEIS'[Marca]) &&
            'FACT AUTOMOVEIS'[Modelo] = EARLIER('FACT AUTOMOVEIS'[Modelo])
        ),
        'FACT AUTOMOVEIS'[Preço]
    )
VAR StdevPreco =
    STDEVX.P(
        FILTER(
            'FACT AUTOMOVEIS',
            'FACT AUTOMOVEIS'[Marca] = EARLIER('FACT AUTOMOVEIS'[Marca]) &&
            'FACT AUTOMOVEIS'[Modelo] = EARLIER('FACT AUTOMOVEIS'[Modelo])
        ),
        'FACT AUTOMOVEIS'[Preço]
    )
RETURN
- IF(
    ISBLANK(AVGPreco) || ISBLANK(StdevPreco) || StdevPreco = 0 || ISBLANK('FACT AUTOMOVEIS'[Preço]),
    BLANK(),
    ('FACT AUTOMOVEIS'[Preço] - AVGPreco) / StdevPreco
)
Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    3 years ago

    Vitorino it will be easier to provide a solution if you paste sample data and expected output.

     

    👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

     

     

    • Vitorino's avatar
      Vitorino
      Regular Visitor
      3 years ago

      So, I have this car dataset:
      (marca = brand, modelo = model, Ano = Year, Kms = Kilometers, Preço = Price, preço normalizado = Normalized price, AVG Preço = average price, STD Preço = Price standard deviation, MAX and MIN Price)

       

       

      So, it seems that when I use the provided calculated column above (preço normalizado), which creates a normalized price, it includes all brands (marca) and models (modelos). However, what I want is for that value to recalculate when I apply filters to the brand or models. In this example, the brand "CLA 180" and the model "CLA 200" should be calculated separately, but what is currently happening is that they are sharing the same scale for all cars.
      So, I thought that I should probably use a measure to adjust the calculations, but it didn't work out as expected.

      I also calculate the standard deviation (STDeviation), maximum price (Max€), and minimum price (MIN€).