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 Community!
I have a Table with the first 2 columns of the following example (ID and Time_1), and I need to add a third column (Time_2), in order to get something like this:
ID | Time_1 | Time_2 |
1 | 30 | 10 |
1 | 30 | 10 |
1 | 30 | 10 |
2 | 50 | 50 |
3 | 40 | 20 |
3 | 40 | 20 |
4 | 80 | 20 |
4 | 80 | 20 |
4 | 80 | 20 |
4 | 80 | 20 |
Taking ID = 15 as example, Time_2 should be calculated as 30/3 = 10 (30 is the value of Time_1 and 3 is how many times Order 15 is repeated).
I’d be grateful if I could get some help. Thanks in advance
Solved! Go to Solution.
@aceremin - Try this:
Time_2 =
VAR __Time_1 = [Time_1]
VAR __Num = COUNTROWS(FILTER('Table (18)',[ID]=EARLIER([ID])))
RETURN
__Time_1/__Num
PBIX is attached below sig. Table (18).
@aceremin ,
Try this formula:
Hi @nandic
Thanks for your help.
But I am not getting the result I was expecting.
The column Time_2 shows me the Time_1 value.
Looks like _Id_Count is not counting the number of times the ID appears in the column.
I tried creating the column _Id_Count separatelly, and I get 1 as a result for each row.
In my example I should get something like:
I hope this is clear. Do you know what I should do?
Thanks again!
@aceremin - Try this:
Time_2 =
VAR __Time_1 = [Time_1]
VAR __Num = COUNTROWS(FILTER('Table (18)',[ID]=EARLIER([ID])))
RETURN
__Time_1/__Num
PBIX is attached below sig. Table (18).
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |