Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.