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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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