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
Below is a piece of the fact table 'fTrans' that provides context to my question.
Ticker | Date | Sale date related to respective purchase | Days | Transaction | Shares | Price | Purchase total |
ABEV3 | 01/Mar/2021 | 04/Jun/2021 | 95 | Purchase | 77 | $12.93 | $995.61 |
ABEV3 | 04/May/2021 | 04/Jun/2021 | 31 | Purchase | 73 | $13.60 | $992.80 |
ABEV3 | 04/Jun/2021 | Sale | 150 | $17.91 | |||
ABEV3 | 28/Jun/2021 | 29/Oct/2021 | 123 | Purchase | 62 | $16.01 | $992.62 |
ABEV3 | 29/Oct/2021 | Sale | 62 | $15.62 | |||
ALSO3 | 07/Jan/2021 | 03/Feb/2021 | 27 | Purchase | 39 | $25.12 | $979.68 |
ALSO3 | 03/Feb/2021 | Sale | 39 | $27.20 | |||
ALSO3 | 29/Jun/2021 | 30/Mar/2022 | 274 | Purchase | 36 | $27.53 | $991.08 |
ALSO3 | 07/Jan/2022 | 30/Mar/2022 | 82 | Purchase | 56 | $17.85 | $999.60 |
ALSO3 | 30/Mar/2022 | Sale | 92 | $22.61 | |||
APER3 | 18/Nov/2021 | 22/Dec/2021 | 34 | Purchase | 33 | $29.88 | $986.04 |
APER3 | 22/Dec/2021 | Sale | 33 | $33.40 | |||
APER3 | 14/Jun/2022 | 11/Aug/2022 | 58 | Purchase | 34 | $29.14 | $990.76 |
APER3 | 04/Aug/2022 | 11/Aug/2022 | 7 | Purchase | 42 | $23.57 | $989.94 |
APER3 | 11/Aug/2022 | Sale | 76 | $30.33 |
As you can see for all rows whose [Transaction] column is a "Sale" the [Days] column has empty values.
The reason being that there are cases where a "Sale" transaction relates to more than one previous "Purchase" transaction, therefore a weighted average of [Days] needs to be calculated for those "Sale" rows.
So, my goal is to write up a measure that will provide a weighted average of [Days] for every [Transaction] "Sale" rows that are currently sitting empty, and in order to get there I started with this:
EVALUATE
VAR Supp_Tbl =
ADDCOLUMNS(
SUMMARIZE(
fTrans,
fTrans[Ticker],
fTrans[Date],
fTrans[Sale date related to respective purchase],
fTrans[Transaction],
"@Days", SUMX(
fTrans,
IF(
fTrans[Transaction] = "Sale",
BLANK(),
INT( fTrans[Sale date related to respective purchase] - fTrans[Date] )
)
),
"@PurchTotal", [Purchase total]
),
"@WghtProdDaysPurch", [@Days] * [@PurchTotal]
)
VAR Days_Tbl =
ADDCOLUMNS(
GROUPBY(
Supp_Tbl,
fTrans[Ticker],
fTrans[Sale date related to respective purchase],
"@SumPurchTotal", SUMX(
CURRENTGROUP(),
[@PurchTotal]
),
"@SumWghtProdDaysPurch", SUMX(
CURRENTGROUP(),
[@WghtProdDaysPurch]
)
),
"@WghtdAvgDays", INT(
DIVIDE(
[@SumWghtProdDaysPurch],
[@SumPurchTotal]
)
)
)
RETURN
Days_Tbl
And it returns the following temporary table VAR "Days_Tbl" whose highlighted [@WghtdAvgDays] provides said weighted average of [Days] that I need for those "Sale" transaction rows:
Ticker | Sale date related to respective purchase | @SumPurchTotal | @SumWghtProdDaysPurch | @WghtdAvgDays |
ABEV3 | ||||
ABEV3 | 04/Jun/2021 | 1,988.41 | 125,359.75 | 63 |
ABEV3 | 29/Out/2021 | 992.62 | 122,092.26 | 123 |
ALSO3 | ||||
ALSO3 | 03/Feb/2021 | 979.68 | 26,451.36 | 27 |
ALSO3 | 30/Mar/2022 | 1,990.68 | 353,523.12 | 177 |
APER3 | ||||
APER3 | 22/Dec/2021 | 986.04 | 33,525.36 | 34 |
APER3 | 11/Aug/2022 | 1,980.7 | 64,393.66 | 32 |
Now all I need is to get those values from [@WghtdAvgDays] under VAR "Days_Tbl" whenever [Sale date related to respective purchase] matches fTrans[Date] under each [Ticker].
And for that I first tried by writing the following code:
LOOKUPVALUE(
[@WghtdAvgDays],
fTrans[Sale date related to respective purchase],
MAXX(
FILTER(
fTrans,
fTrans[Ticker] = EARLIER( fTrans[Ticker] ) &&
fTrans[Date] = EARLIER( fTrans[Date] ) &&
fTrans[Transaction] = "Sale"
),
fTrans[Date]
)
)
But it right off the bat presents two issues:
How am I supposed to get the values from the [@WghtdAvgDays] column under the temporary table VAR "Days_Tbl" then?
Thanks in advance...
P.S.:
I also tried the route of creating a third temp table with SUMMARIZECOLUMNS but it ends up aggregating the overall total of [Days] for all rows:
FILTER(
SUMMARIZECOLUMNS(
fTrans[Ticker],
fTrans[Date],
fTrans[Transaction],
"@Days", CALCULATE(
SUMX(
Days_Tbl,
[@WghtdAvgDays]
),
fTrans[Sale date related to respective purchase] = fTrans[Date]
)
),
fTrans[Transaction] = "Sale"
)
Ticker | Date | Transaction | Days |
ABEV3 | 04/Jun/2021 | Sale | 30,080 |
ABEV3 | 29/Out/2021 | Sale | 30,080 |
ALSO3 | 03/Feb/2021 | Sale | 30,080 |
ALSO3 | 30/Mar/2022 | Sale | 30,080 |
APER3 | 22/Dec/2021 | Sale | 30,080 |
APER3 | 11/Aug/2022 | Sale | 30,080 |
Solved! Go to Solution.
@lbendlin your approach sure provided the stepping stones for helping me get across the finish line.
Thanks very much for your valuable input!
Just a couple of tweaks to your code were needed to reach the goal:
So the final code looks as follows:
Table =
ADDCOLUMNS(
SUMMARIZE(
FILTER(
fTrans,
fTrans[Transaction] = "Sale"
),
fTrans[Ticker],
fTrans[Date]
),
"WavgDays",
VAR Ticker_Ref = [Ticker]
VAR Date_Ref = [Date]
RETURN
CALCULATE(
SUMX( fTrans,
DIVIDE(
[Days] * [Purchase total],
CALCULATE(
[Purchase total],
ALL( fTrans ),
fTrans[Ticker] = Ticker_Ref,
fTrans[Sale date related to respective purchase] = Date_Ref
)
)
),
ALL( fTrans ),
fTrans[Ticker] = Ticker_Ref,
fTrans[Sale date related to respective purchase] = Date_Ref
)
)
Thanks!
@lbendlin your approach sure provided the stepping stones for helping me get across the finish line.
Thanks very much for your valuable input!
Just a couple of tweaks to your code were needed to reach the goal:
So the final code looks as follows:
Table =
ADDCOLUMNS(
SUMMARIZE(
FILTER(
fTrans,
fTrans[Transaction] = "Sale"
),
fTrans[Ticker],
fTrans[Date]
),
"WavgDays",
VAR Ticker_Ref = [Ticker]
VAR Date_Ref = [Date]
RETURN
CALCULATE(
SUMX( fTrans,
DIVIDE(
[Days] * [Purchase total],
CALCULATE(
[Purchase total],
ALL( fTrans ),
fTrans[Ticker] = Ticker_Ref,
fTrans[Sale date related to respective purchase] = Date_Ref
)
)
),
ALL( fTrans ),
fTrans[Ticker] = Ticker_Ref,
fTrans[Sale date related to respective purchase] = Date_Ref
)
)
Thanks!
I would run this the other way round, starting with the Sale dates.
I don't think you need a measure for this - the results don't seem to be impacted by user filter choices.
Table = ADDCOLUMNS(SUMMARIZE(FILTER(fTrans,[Transaction]="Sale"),[Ticker],[Date]),"WavgDays",var t = [Ticker] var d=[Date] return CALCULATE(sumx(fTrans,fTrans[Days]*fTrans[Purchase total]),all(fTrans),fTrans[Ticker]=t,fTrans[Sale date related to respective purchase]=d))
I created a measure that would allow me to plop that [WavgDays] column from your calculated table in whatever visual I may need (on the case below, a simple table), but I always keep getting the overall total of days:
Ticker | Date | Days | Transaction | Shares | Price | Purchase total | Days average 1 | Days average 2 |
ABEV3 | 01/Mar/2021 | 95 | Purchase | 77 | $12.93 | $995.61 | ||
ABEV3 | 04/May/2021 | 31 | Purchase | 73 | $13.60 | $992.80 | ||
ABEV3 | 04/Jun/2021 | Sale | 150 | $17.91 | 29,360 | 29,360 | ||
ABEV3 | 28/Jun/2021 | 123 | Purchase | 62 | $16.01 | $992.62 | ||
ABEV3 | 29/Oct/2021 | Sale | 62 | $15.62 | 29,360 | 29,360 | ||
ALSO3 | 07/Jan/2021 | 27 | Purchase | 39 | $25.12 | $979.68 | ||
ALSO3 | 03/Feb/2021 | Sale | 39 | $27.20 | 29,360 | 29,360 | ||
ALSO3 | 29/Jun/2021 | 274 | Purchase | 36 | $27.53 | $991.08 | ||
ALSO3 | 07/Jan/2022 | 82 | Purchase | 56 | $17.85 | $999.60 | ||
ALSO3 | 30/Mar/2022 | Sale | 92 | $22.61 | 29,360 | 29,360 | ||
APER3 | 18/Nov/2021 | 34 | Purchase | 33 | $29.88 | $986.04 | ||
APER3 | 22/Dec/2021 | Sale | 33 | $33.40 | 29,360 | 29,360 | ||
APER3 | 14/Jun/2022 | 58 | Purchase | 34 | $29.14 | $990.76 | ||
APER3 | 04/Aug/2022 | 7 | Purchase | 42 | $23.57 | $989.94 | ||
APER3 | 11/Aug/2022 | Sale | 76 | $30.33 | 29,360 | 29,360 |
I tried both these codes to no success.
Days average 1 =
IF(
VALUES( fTrans[Transaction] ) = "Sale",
SUMX(
fWavgDays,
fWavgDays[WavgDays]
)
)
___________________________________________________________
Days average 2 =
VAR Ticker_Ref = VALUES( fTrans[Ticker] )
VAR Date_Ref = VALUES( fTrans[Date] )
RETURN
CALCULATE(
IF(
VALUES( fTrans[Transaction] ) = "Sale",
SUM( fWavgDays[WavgDays] )
),
fTrans[Ticker] = Ticker_Ref,
fTrans[Date] = Date_Ref
)
What am I doing wrong?
[Days] and [Purchase total] are measures instead of columns of table 'fTrans', so all it was needed was to remove 'fTrans' from both within SUMX.
why?
Because both measures are located on another table I've got just to place the measures I create.
You will need to indicate what these measures do. You cannot convert a calculated column formula into a measure formula like that - you need to think deeply about the very different concepts, and most of the time you need to write a completely new measure from scratch.
@lbendlinI thought that once I came up with the calculated column I wanted to get, namely [WavgDays], I could create a measure using CALCULATE to iterate row by row through the table visual and pick up the value from [WavgDays] on the calculated table 'fWavgDays' to each "Sale" row on the table visual whose respective [Ticker] and [Date] match those on 'fWavgDays'.
Such measure would be something similar to a RELATED type process. Or talking in Excel terms, an INDEX/MATCH type deal, where the visual table is the destination and the source table on this case is 'fWavgDays'.
You cannot create a calculated column from measures (as you seem to be trying to do). You may need to start over (as I mentioned above).
Please provide sample data that fully covers your issue.
@lbendlin I believe I didn't make myself clear and I apologize for that...
The table from the above reply that originated this follow-up question is not a calculated table, but just some sample rows from a table visual.
Below is a screenshot of the visual, which is basically a mirror of the table previously shown (I hadn't originally provided a screenshot because often times when I do screenshots on Stack Overflow I get frowned upon, so I guess I got bitten by the web shame snake...).
With that said, both columns in red "Days average 1" and "Days average 2" are not calculated columns on a calculated table, but measures dropped on a table visual whose codes are the ones previously provided.
So as I mentioned on my last note, once reaching my first goal of figuring out a way to calculate weighted average days for sales transactions, which you helped me do it by getting to [WavgDays], then all I needed to do after that was to come up with a measure that retrieves values from that calculated column [WavgDays], and that's what I'm trying to do now.
I thought CALCULATE could do that job since:
But I'm either doing something wrong with CALCULATE that's instead providing the overall total of days for every row, or CALCULATE shouldn't be used at all and instead I should take another route. Either way, I was hoping you could help me point to the right direction here...
Thanks for your patience and willingness to help!
After you create the calculated table it will not automatically be included in the data model. It is a "disconnected" table. That is the reason why you see the same number all over the column/measure. Wire the table into your data model as needed.
@lbendlin that's the thing though, prior to creating that measure I did make sure to connect 'fWavgDays' calculated table to the the data model via a one-to-many relationship coming from the date table 'dDate'[Data] as shown below:
And looking at your originally suggested code, which I repost below, it looks like the date table 'dDate' is not being referenced, which makes sense since you didn't know I had a date table in my model. Do you think that could be the issue? And if so, should the below code be re-written or should I wire 'fTrans' to the calculated table 'fWavgDays' via [Date]? Such connection would have to be a many-to-many relationship so I'm not sure if that could bring up other issues to the model...
Table = ADDCOLUMNS(SUMMARIZE(FILTER(fTrans,[Transaction]="Sale"),[Ticker],[Date]),"WavgDays",var t = [Ticker] var d=[Date] return CALCULATE(sumx(fTrans,fTrans[Days]*fTrans[Purchase total]),all(fTrans),fTrans[Ticker]=t,fTrans[Sale date related to respective purchase]=d))
Thanks!
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Mention everything that is related to the issue. Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
@lbendlin please find below the link to the shared .pbix file related to this issue. Please let me know in case it doesn't work.
I tried resetting the connection between 'fWavgsDays' calculated table and the rest of the data model via 'fTrans' with a many-to-many relationship from/to both [Date] columns (also had to get rid of the one-to-many connection with the date table in order to make the newly created connection active).
And it still didn't work as per the following screenshot (in blue are the hardcoded correct results). Now it's apparently summing up [WavgDays] for each [Date] for all [Ticker], so it looks like the correct connection setup is the one-to-many between 'fWavgsDays' and 'dDate' and the calculated table code somehow needs to reference 'dDate'[Data].
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 |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |