cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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:

 

MonthQuantityPriceTurnover
1145         1,187             172,115
2149         1,187             176,863
3182         1,187             216,034
4155         1,187             183,985
5111         1,187             131,757
6128         1,187             151,936
7105         1,187             124,635
8142         1,187             168,554
9118         1,163             137,234
10162         1,163             188,406
11175         1,163             203,525
12155         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
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])
                                            )
   )

View solution in original post

10 REPLIES 10
Highlighted
Super User I
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
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
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
Helper I

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

With the real data.

Highlighted
Super User I
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
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:

 

FactPrice.jpg

 

This table is related to dimDates with the dimension DateKey:

 

Model.jpg

 

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
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.

Or link your pbix.

Thanks

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

 

Thanks again for your help!

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

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

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

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.