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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
leolapa_br
Resolver I
Resolver I

Get values from a temporary table variable

Below is a piece of the fact table 'fTrans' that provides context to my question.

 

TickerDateSale date related to respective purchaseDaysTransactionShares PricePurchase total
ABEV301/Mar/202104/Jun/202195Purchase77$12.93$995.61
ABEV304/May/202104/Jun/202131Purchase73$13.60$992.80
ABEV304/Jun/2021  Sale150$17.91 
ABEV328/Jun/202129/Oct/2021123Purchase62$16.01$992.62
ABEV329/Oct/2021  Sale62$15.62 
ALSO307/Jan/202103/Feb/202127Purchase39$25.12$979.68
ALSO303/Feb/2021  Sale39$27.20 
ALSO329/Jun/202130/Mar/2022274Purchase36$27.53$991.08
ALSO307/Jan/202230/Mar/202282Purchase56$17.85$999.60
ALSO330/Mar/2022  Sale92$22.61 
APER318/Nov/202122/Dec/202134Purchase33$29.88$986.04
APER322/Dec/2021  Sale33$33.40 
APER314/Jun/202211/Aug/202258Purchase34$29.14$990.76
APER304/Aug/202211/Aug/20227Purchase42$23.57$989.94
APER311/Aug/2022  Sale76$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:

 

TickerSale date related to respective purchase@SumPurchTotal@SumWghtProdDaysPurch@WghtdAvgDays
ABEV3    
ABEV304/Jun/20211,988.41125,359.7563
ABEV329/Out/2021992.62122,092.26123
ALSO3    
ALSO303/Feb/2021979.6826,451.3627
ALSO330/Mar/20221,990.68353,523.12177
APER3    
APER322/Dec/2021986.0433,525.3634
APER311/Aug/20221,980.764,393.6632

 

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?

 

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

 

TickerDateTransactionDays
ABEV304/Jun/2021Sale30,080
ABEV329/Out/2021Sale30,080
ALSO303/Feb/2021Sale30,080
ALSO330/Mar/2022Sale30,080
APER322/Dec/2021Sale30,080
APER311/Aug/2022Sale 30,080

 

1 ACCEPTED SOLUTION
leolapa_br
Resolver I
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 = 
	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!

View solution in original post

13 REPLIES 13
leolapa_br
Resolver I
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 = 
	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
Super User
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))

 

lbendlin_0-1692754639444.png

 

@lbendlin 

 

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:

 

TickerDateDaysTransactionSharesPricePurchase totalDays average 1Days average 2
ABEV301/Mar/202195Purchase77$12.93$995.61  
ABEV304/May/202131Purchase73$13.60$992.80  
ABEV304/Jun/2021 Sale150$17.91 29,36029,360
ABEV328/Jun/2021123Purchase62$16.01$992.62  
ABEV329/Oct/2021 Sale62$15.62 29,36029,360
ALSO307/Jan/202127Purchase39$25.12$979.68  
ALSO303/Feb/2021 Sale39$27.20 29,36029,360
ALSO329/Jun/2021274Purchase36$27.53$991.08  
ALSO307/Jan/202282Purchase56$17.85$999.60  
ALSO330/Mar/2022 Sale92$22.61 29,36029,360
APER318/Nov/202134Purchase33$29.88$986.04  
APER322/Dec/2021 Sale33$33.40 29,36029,360
APER314/Jun/202258Purchase34$29.14$990.76  
APER304/Aug/20227Purchase42$23.57$989.94  
APER311/Aug/2022 Sale76$30.33 29,36029,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...).

 

Table_Visual.png

 

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!

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:

 

Data_Model.png

 

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.

 

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

 

Data_Model_2.png

 

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

 

Table_Visual.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors