cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Display contributing values to max value

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.

 

Usage Example.PNG

 

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Display contributing values to max value

@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])

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Super User IV
Super User IV

Re: Display contributing values to max value

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Display contributing values to max value

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

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Display contributing values to max value

@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])

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Super User IV
Super User IV

Re: Display contributing values to max value

Oh, and here is the PBIX. Page 2, Table1.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Display contributing values to max value

Thanks! I was able to get to where I needed using that.

 

I was looking for a table like the one on the right.

Max Usage Example.PNG

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])
Super User IV
Super User IV

Re: Display contributing values to max value

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors