cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Resolver I

## Get values from a temporary table variable

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 =
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 =
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:

1. The column 'fTrans[Sale date related to respective purchase]' either doesn't exist or doesn't have a relationship to any table available in the current context (message given by PBI when committing the measure code).
2. [@WghtdAvgDays] can't be used as the first argument of LOOKUPVALUE as it expects a fully qualified column (message provided by DAX Studio while attempting to troubleshoot the code).

How am I supposed to get the values from the [@WghtdAvgDays] column under the temporary table VAR "Days_Tbl" then?

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

1 ACCEPTED SOLUTION
Resolver I

@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:

• [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.
• Your calculation provides the equivalent in Excel of a SUMPRODUCT between [Days] and [Purchase total], so all it was needed in order to get a proper weighted average of [Days] was to divide that by the SUM of [Purchase total], and I did that by wrapping it around CALCULATE with the same filters (at least to me it somewhat looked odd a CALCULATE within another CALCULATE, but that's the only way I know how to do it for that particular case - perhaps creating a VAR for that would be more within the best practice guidelines).

So the final code looks as follows:

Table =
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!

13 REPLIES 13
Resolver I

@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:

• [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.
• Your calculation provides the equivalent in Excel of a SUMPRODUCT between [Days] and [Purchase total], so all it was needed in order to get a proper weighted average of [Days] was to divide that by the SUM of [Purchase total], and I did that by wrapping it around CALCULATE with the same filters (at least to me it somewhat looked odd a CALCULATE within another CALCULATE, but that's the only way I know how to do it for that particular case - perhaps creating a VAR for that would be more within the best practice guidelines).

So the final code looks as follows:

Table =
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!

Super User

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

Resolver I

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?

Super User
[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?

Resolver I

Because both measures are located on another table I've got just to place the measures I create.

Super User

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.

Resolver I

@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'.

Super User

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

Resolver I

@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:

1. Together with a row iterator funcion should iterate row by row on whatever table we provide (on this case 'fWavgDays').
2. It therefore allows to relate to the calculated column [WavgDays] on its first expression argument.
3. Narrows down to the matching [Ticker] and [Date] on its second filter argument.

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!

Super User

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.

Resolver I

@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!

Super User

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.

Resolver I

@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.

Investm_Import_Ctrl_TBD.pbix

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

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors