topic Re: Creating measure using several aggregating values in DAX Commands and Tips

Creating measure using several aggregating values

Anonymous — Tue, 22 Oct 2019 10:17:24 GMT

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 !

Re: Creating measure using several aggregating values

Anonymous — Tue, 22 Oct 2019 10:47:24 GMT

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)

Re: Creating measure using several aggregating values

Anonymous — Tue, 22 Oct 2019 12:36:34 GMT

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

Re: Creating measure using several aggregating values

Anonymous — Tue, 22 Oct 2019 14:10:46 GMT

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