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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Joris_NL
Helper II
Helper II

Need percentage of total after SUMX - SUMMARIZE

Hi,

 

I'm struggling for hours now. 

I have data like this:

 

Person  ContractHours  SomeRecordNotRelevant  
12015
12016
23017
23018
31019

 

I need an answer like this:

 

Person 1 who has 20 hours = 33% (20 out of 20+30+10 total hours)
Person 2  with 30 hours = 50%

Person 3 = 16,7%

So what I tried is a measure like this:

 

VAR HoursOverview =
SUMMARIZE('Table' ; 'Table'[Person] ; "Hours" ; MAX ( 'Table'[ContractHours] ))

VAR HoursPerPerson =
SUMX ( HoursOverview ; [Hours] )
 
VAR GrandTotalHours =
???
 
RETURN
DIVIDE ( HoursPerPerson ; GrandTotalHours ; 0 )
 
Which makes me clueless, because ALL or FILTER functions don't work on the VAR tables, so I can't get a Grand Total. Please help!
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Try using ALL or ALLSELECTED for calculating the grand total hours.

 

VAR AllHoursOverview =
    SUMMARIZE (
        ALLSELECTED ( 'Table' );
        'Table'[Person];
        "Hours"; MAX ( 'Table'[ContractHours] )
    )
VAR HoursOverview =
    SUMMARIZE ( 'Table'; 'Table'[Person]; "Hours"; MAX ( 'Table'[ContractHours] ) )
VAR HoursPerPerson =
    SUMX ( HoursOverview; [Hours] )
VAR GrandTotalHours =
    SUMX ( AllHoursOverview; [Hours] )
RETURN
    DIVIDE ( HoursPerPerson; GrandTotalHours; 0 )

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

Try using ALL or ALLSELECTED for calculating the grand total hours.

 

VAR AllHoursOverview =
    SUMMARIZE (
        ALLSELECTED ( 'Table' );
        'Table'[Person];
        "Hours"; MAX ( 'Table'[ContractHours] )
    )
VAR HoursOverview =
    SUMMARIZE ( 'Table'; 'Table'[Person]; "Hours"; MAX ( 'Table'[ContractHours] ) )
VAR HoursPerPerson =
    SUMX ( HoursOverview; [Hours] )
VAR GrandTotalHours =
    SUMX ( AllHoursOverview; [Hours] )
RETURN
    DIVIDE ( HoursPerPerson; GrandTotalHours; 0 )

Thanks, it works!

I doubt my 4 variables are a clean solution, but I don't know any better atm and it works for now. 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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