Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
Solved! Go to 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]) ) )
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:
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:
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
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
12 |