Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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/2019 | 0.37 |
3/16/2019 | 0.4 |
3/16/2019 | 0.36 |
4/27/2019 | 0.02 |
5/11/2019 | 1.36 |
5/11/2019 | 0.94 |
Table 2 | |
Date | Number |
3/2/2019 | 404.7 |
3/16/2019 | 405 |
4/27/2019 | 405.75 |
5/11/2019 | 406.1 |
Output I want:
Output | |
Date | Percent |
3/2/2019 | 0.22% |
3/2/2019 | 0.01% |
3/16/2019 | 0.09% |
3/16/2019 | 0.10% |
3/16/2019 | 0.09% |
4/27/2019 | 0.00% |
5/11/2019 | 0.33% |
5/11/2019 | 0.23% |
I was trying this but it's not working:
Percentage = Divide(sum(Table1[Decimal],Lookupvalue(Table2[Number],Table2[Date],Table1[Date]),0)
Can Lookupvalue be used for dates?
Any help would be appreciated. TYIA.
Solved! Go to Solution.
@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:
Hi,
This calculated column works just fine.
Column = [Decimal]/LOOKUPVALUE(Table2[Number],Table2[Date],Table1[Date])
Hope this helps.
@Ashish_Mathur wrote:Hi,
This calculated column works just fine.
Column = [Decimal]/LOOKUPVALUE(Table2[Number],Table2[Date],Table1[Date])Hope this helps.
This doesn't work at all for me.
It says: "The value for 'Decimal' cannot be determined. Either 'Decimal' doesn't exist, or there is no current row for a column named 'Decimal'.
?? please let me know if you have any ideas.
Hi,
I cannot understand why it is not working. As can be seen in my screenshot, my formula works fine. I hope you are writing it as a calculated column formula (not as a measure).
hi,
you can use calculated column:
Calculated Column % = DIVIDE(Table1[Decimal], LOOKUPVALUE(Table2[Number],Table2[Date],Table1[Date]))
here is the PBI file:
https://1drv.ms/u/s!Aps8poidQa5zk6pa5KcbigH1gCmIYg
@Iamnvt wrote:hi,
you can use calculated column:
Calculated Column % = DIVIDE(Table1[Decimal], LOOKUPVALUE(Table2[Number],Table2[Date],Table1[Date]))here is the PBI file:
https://1drv.ms/u/s!Aps8poidQa5zk6pa5KcbigH1gCmIYg
I took it back a step and tried just getting the "Lookupvalue" function to work.
Each time when I get to "Table2[Date] I get options for ".Date" ".Month" etc and after I chose one there is no option to pick "Table1[Date]" as my third attribute.
If I manually type over or copy and paste what you have above, I get this error:
"A single value for column 'Date' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Please let me know if you have any ideas on the error? TY.
@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:
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
96 | |
79 | |
66 | |
62 |
User | Count |
---|---|
145 | |
113 | |
105 | |
85 | |
65 |