cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Super User

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
Super User

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

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?

Super User

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

Helper I

With the real data.

Super User

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

Helper I

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?

Super User

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

Helper I

Hi,

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

Super User

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

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

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors