Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Creating measure using several aggregating values

Hello everyone, 

 

I'ms struggling in building 3 different measure in Dax Syntax. In fact, I would like to calculate a measure based on the volume into a row divide by the sum of volume of several rows. Here is my usecase :

 

I have a table of country, brand, volumes and price such as below :

I would like to calculate 3 differents measures. 

The first one is the proportion (in volume) of each brand into a specific country. 

The second one is the difference of price (at a row level) with the average price at a country level.

The third one is the profit by row divided by the total volume of the country. 

Here is the expected table in output : 

 

Can someone help me about this stuff ? 

 

Thank you in advance !  

  • Anonymous's avatar
    Anonymous
    6 years ago

    First := DIVIDE(SUM(Test[Volume]),CALCULATE(SUM(Test[Volume]),ALLEXCEPT(Test,Test[Country])),0)

    Second = SUM(Test[Price])-CALCULATE(AVERAGE(Test[Price]),ALLEXCEPT(Test,Test[Country]))
    Third = DIVIDE(SUMX(Test,Test[Volume]*Test[Price]),CALCULATE(SUM(Test[Volume]),ALLEXCEPT(Test,Test[Country])),0)

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    First := DIVIDE(SUM(Test[Volume]),CALCULATE(SUM(Test[Volume]),ALLEXCEPT(Test,Test[Country])),0)

    Second = SUM(Test[Price])-CALCULATE(AVERAGE(Test[Price]),ALLEXCEPT(Test,Test[Country]))
    Third = DIVIDE(SUMX(Test,Test[Volume]*Test[Price]),CALCULATE(SUM(Test[Volume]),ALLEXCEPT(Test,Test[Country])),0)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you very much Anonymous, I will test the measure and let you know about the results. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        It works perfectly !!! Thank you very much Anonymous to make it so easy !