Forum Discussion
Lookupvalue using dates?
Hello, I am trying to divide a number in Table 1 by a number with the corresponding date in Table 2 to get a percentage. Table 1 Date Decimal 3/2/2019 0.9 3/2/2019 0.05 3/16/2...
Iamnvt wrote:
It looks like you are writing a Measure, not calculated column. I think you should turn off the date hierarchy to have [.Month] off.
You can go to Model tab, and insert calculated column with the said formula. Check my PBI file for more details.Just want to close the loop on the question and address the date hierarchy.
The date hierarchy has been off and still the .[Month] etc. comes up.
I ended up using:
Measure = Divide(sum('Table1'[Decimal]),calculate(sum('Table2'[Number])),0)which seems to work. TY
Hello, thank you for responding. What is the difference between what you posted and what I have in my question? The only difference I see is the "sum" portion. My issue is the lookupvalue does not work when I write it that way. After I put in the date column, I get a drop down that gives the following choices: .[Date], .[Day], .[Month], .[MonthNo], .[Quarter], .[QuarterNo], .[Year]
no matter which one I pick, it doesn't work.
Could it be an issue with the relationship between the tables?
Iamnvt
Continued Contributor
Continued ContributorHi,
In calculated column, it is refer to row context. When you use SUM in row context, it will sum up all the row values. You need to enable context transition to make SUM refer to one row.
You can do that by wrap up SUM with CALCULATE —> Calculate(Sum([Values]))
You need to have a separate Calendar table to link between the 2 tables. If that the case, then you dont need to use lookupvalue, but just DIVDE(SUM([Decimal],sum([Number]))
In calculated column, it is refer to row context. When you use SUM in row context, it will sum up all the row values. You need to enable context transition to make SUM refer to one row.
You can do that by wrap up SUM with CALCULATE —> Calculate(Sum([Values]))
You need to have a separate Calendar table to link between the 2 tables. If that the case, then you dont need to use lookupvalue, but just DIVDE(SUM([Decimal],sum([Number]))