Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |