Forum Discussion
Get values from a temporary table variable
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!
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:
- Together with a row iterator funcion should iterate row by row on whatever table we provide (on this case 'fWavgDays').
- It therefore allows to relate to the calculated column [WavgDays] on its first expression argument.
- 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:
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-Forum/ba-p/963216
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/1447523lbendlin 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].