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.
In the example below I have consumption values for each user over a 10 hour period. I'm trying to show what the max consumption is and what values contribute to that. In the example below, Hour 9 has the most consumption with 10 kW, and the contribution from each user is 8 from User A and 2 from User B.
I'd like to display the Max value of 10, and the contribution from each user towards that value. I've made a couple of measures so far but have only been able to display the max value for each user along with the overall max value of 10.
Measure 1 = sum(usage)
Measure 2 = Maxx(value(hour), Measure 1)
Solved! Go to Solution.
@Anonymous - Here is the last bit, individual contribute per user for max usage hour.
Measure 4 = VAR __user = MAX([User]) VAR __table = SUMMARIZE(ALL('Table1'),[Hour],"__usage",[Measure 1]) VAR __maxUsage = MAXX(__table,[__usage]) VAR __maxHour = MAXX(FILTER(__table,[__usage]=__maxUsage),[Hour]) RETURN MAXX(FILTER(ALL('Table1'),[User] = __user && [Hour] = __maxHour),[Usage])
Oh, and here is the PBIX. Page 2, Table1.
Thanks! I was able to get to where I needed using that.
I was looking for a table like the one on the right.
I created Measure 5 to filter by user and return either measure 4 or measure 2 based on the user being filtered.
Measure 5 = If(ISFILTERED(Table1[User]),[Measure 4],[Measure 2])
Very nice! I forgot about accounting for the total but you solved that issue just like how I do it in Measure Totals, The Final Word:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
@Anonymous - Here is the last bit, individual contribute per user for max usage hour.
Measure 4 = VAR __user = MAX([User]) VAR __table = SUMMARIZE(ALL('Table1'),[Hour],"__usage",[Measure 1]) VAR __maxUsage = MAXX(__table,[__usage]) VAR __maxHour = MAXX(FILTER(__table,[__usage]=__maxUsage),[Hour]) RETURN MAXX(FILTER(ALL('Table1'),[User] = __user && [Hour] = __maxHour),[Usage])
Here is your Max Hour:
Measure 3 = VAR __table = SUMMARIZE('Table1',[Hour],"__usage",[Measure 1]) VAR __maxUsage = MAXX(__table,[__usage]) VAR __maxHour = MAXX(FILTER(__table,[__usage]=__maxUsage),[Hour]) RETURN __maxHour
OK, for the first part, you want:
Measure 2 = VAR __table = SUMMARIZE('Table1',[Hour],"__usage",[Measure 1]) VAR __maxUsage = MAXX(__table,[__usage]) RETURN __maxUsage
This will give you your max usage of 10 number. I'll have the second part here in a minute.
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |