cancel
Showing results for
Did you mean:
Highlighted Helper I

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

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?
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User I

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

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])
)
)```
10 REPLIES 10
Highlighted Super User I

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

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]```
Highlighted Helper I

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

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?

Highlighted Super User I

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

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

Highlighted Helper I

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

With the real data.

Highlighted Super User I

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

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

Highlighted Helper I

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

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?

Highlighted Super User I

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

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.

Thanks

Highlighted Helper I

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

Hi,

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

Highlighted Super User I

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

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])
)
)```

Announcements #### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge! #### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events. #### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp. Top Solution Authors
Top Kudoed Authors
Users online (1,445)