Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jopezzo
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

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

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?

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

With the real data.

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

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?

 

 

 

 

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

Hi,

 

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

 

Thanks again for your help!

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

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors