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.
Hey guys,
The table on the left is fact table.
The table on the right summarizes the Sum of TWIN by Member ID.
1. How do I create a measure / calculated column to calculate average TWin per day by Member ID.
80680001851 will return 1,180 divide by 6 distinct dates = 196.66 per day
80680003100 will return 410 divide by 4 distinct dates = 102.5 per day
2. How do I repeat the same thing, but limit to only last 3 (Descending Order) distinct dates by Member ID.
80680001851 will return (31 Dec) 30 + (29 Dec) 200 + (27 Dec) 400 = 630 then divide by 3 = 210
80680003100 will return (28 Dec) 30 + (25 Dec) 10 + 190 + (20 Dec) 20 + 80 then divide by 3 = 110
Solved! Go to Solution.
Hi @Anonymous
try these measures:
AVG PEr Day = AVERAGEX( SUMMARIZE( data, data[Member ID], data[Trading Date] ), CALCULATE( SUM( data[TWin] ) ) )
Measure = AVERAGEX( TOPN( 3, SUMMARIZE( data, data[Trading Date], data[Member ID] ), [Trading Date], DESC ), CALCULATE( SUM( data[TWin] ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Anonymous
try these measures:
AVG PEr Day = AVERAGEX( SUMMARIZE( data, data[Member ID], data[Trading Date] ), CALCULATE( SUM( data[TWin] ) ) )
Measure = AVERAGEX( TOPN( 3, SUMMARIZE( data, data[Trading Date], data[Member ID] ), [Trading Date], DESC ), CALCULATE( SUM( data[TWin] ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |