topic How to calculate a turnover with the first price found in a table? in DAX Commands and Tips

How to calculate a turnover with the first price found in a table?

jopezzo — Fri, 07 Jun 2019 09:35:57 GMT

Hi! I am trying to calculate a turnover not based on the price that appears on each row of my fact table, but based on the first price found.

Example:

Month	Quantity	Price	Turnover
1	145	1,187	172,115
2	149	1,187	176,863
3	182	1,187	216,034
4	155	1,187	183,985
5	111	1,187	131,757
6	128	1,187	151,936
7	105	1,187	124,635
8	142	1,187	168,554
9	118	1,163	137,234
10	162	1,163	188,406
11	175	1,163	203,525
12	155	1,163	180,265
			2,035,309

For the turnover, I would have something like:

Turnover =

SUMX(fact_Volumes,fact_Volumes[Price]*fact_Volumes[Quantity])

What I would like is a measure to calculate the turnover with the actual quantity and the first price appearing in the table. In this example I would have all the quantities multiplied by the price 1,187.

Any idea?

Re: How to calculate a turnover with the first price found in a table?

HotChilli — Fri, 07 Jun 2019 14:13:21 GMT

You could add a column. I'm not sure if SELECTCOLUMNS function is better or worse than LOOKUPVALUE in terms of efficiency but it should get you the answer

Column 2 = VAR _minRow = TOPN(1, Table4, Table4[Month], 1)

   VAR _firstPrice = SELECTCOLUMNS(_minRow, "pr", Table4[Price] )
   
  RETURN
   
     _firstPrice * Table4[Quantity]

Re: How to calculate a turnover with the first price found in a table?

jopezzo — Fri, 07 Jun 2019 14:45:31 GMT

Thanks!

I am still getting an error: "A table of multiple values was supplied where a single value was expected."

Last row doesn't accept a column. Any idea?

Re: How to calculate a turnover with the first price found in a table?

HotChilli — Fri, 07 Jun 2019 14:47:17 GMT

Is that with the sample data provided or your real data?

Re: How to calculate a turnover with the first price found in a table?

jopezzo — Fri, 07 Jun 2019 14:50:06 GMT

With the real data.

Re: How to calculate a turnover with the first price found in a table?

HotChilli — Fri, 07 Jun 2019 14:52:09 GMT

OK, do you want to provide a more realistic sample of data? It doesn't have to be real (if the data is confidential) but obviously the real data is more complex

Re: How to calculate a turnover with the first price found in a table?

jopezzo — Thu, 13 Jun 2019 13:33:21 GMT

Hi,

Let's take a more realistic example. I have a table FactPrices:

This table is related to dimDates with the dimension DateKey:

I want to calculate the total revenue based on the first price found for each Material.

What I want to achieve, in this example, is to calculate (12 (first price for Product 1) * 6535) + (17(first price for Product 2) * 6320).

I tried to create the following calculated table:

Summarised Table =

SUMMARIZE (

FactPrices,

FactPrices[Material],

dimDates[DateKey],

"Date",FIRSTDATE(dimDates[Date]),

"Quantity", SUM ( FactPrices[Quantity] ),

"FirstPrice", CALCULATE ( AVERAGE ( FactPrices[Price] ), FIRSTDATE ( dimDates[Date] ) )

)

When doing so, I thouht I would be able to keep only the first prices, while calculating the sum of quantities per Material. Instead, I still get all the prices.

What am I doing wrong?

Re: How to calculate a turnover with the first price found in a table?

HotChilli — Thu, 13 Jun 2019 14:24:16 GMT

Could you post the data (not a picture) , so that i don't have to type it in. In a similar way to how you posted the initial data.

Or link your pbix.

Thanks

Re: How to calculate a turnover with the first price found in a table?

jopezzo — Fri, 14 Jun 2019 10:14:54 GMT

Hi,

I have loaded the .pbix file here: Revenue based on first price

Thanks again for your help!

Re: How to calculate a turnover with the first price found in a table?

HotChilli — Fri, 14 Jun 2019 11:27:43 GMT

Thanks for the data.

I changed the formula to take different Material into account.

Create a column

EarlyPrice Revenue = VAR _CurrMaterial = FactPrices[Material] 
  RETURN
   FactPrices[Quantity] * CALCULATE(SUM(FactPrices[Price]) , 
                                    FILTER(FactPrices, 
                                            FactPrices[Material] = _CurrMaterial && 
                                            FactPrices[DateKey] = MIN(FactPrices[DateKey])
                                            )
   )

Re: How to calculate a turnover with the first price found in a table?

jopezzo — Fri, 14 Jun 2019 12:05:28 GMT

Thanks a lot, HotChilli ! That's what I needed.

Do you need whether I could obtain the same result via a measure?