Average of percent of column totals in DAX

Question

I have a fact table named meetings containing the following: - staff - minutes - type

I then created a summarized table with the following:

TableA = 
    SUMMARIZECOLUMNS ( 
    'meetings'[staff]
    , 'meetings'[type]
    , "SumMinutesByStaffAndType", SUM( 'meetings'[minutes] )
    )

This makes a pivot table with staff as rows and columns as types.

For this pivottable I need to calculate each cell as a percent of the column total. For each staff I need the average of their percents. There are only 5 meeting types so I need the sum of these percents divided by 5.

I don't know how to divide one number grouped by two columns by another number grouped by one column. I'm coming from the SQL world so my DAX is terrible and I'm desperate for advice.

I tried creating another summarized table to get the sum of minutes for each type.

    TableB = 
    SUMMARIZECOLUMNS ( 
        'meetings'[type]
        , "SumMinutesByType", SUM( 'meetings'[minutes] )
    )

From there I want 'TableA'[SumMinutesByStaffAndType] / 'TableB'[SumMinutesByType].

    TableC = 
    SUMMARIZECOLUMNS ( 
        'TableA'[staff],
        'TableB'[type],
        DIVIDE ( 'TableA'[SumMinutesByType], 'TableB'[SumMinutesByType]
     )

"A single value for column 'Minutes' in table 'Min by Staff-Contact' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I keep arriving at this error which leads me to believe I'm not going about this the "Power BI way".

I have tried making measures and creating matrices on the reports view. I've tried using the group by feature in the Query Editor. I even tried both measures and aggregate tables. I'm likely overcomplicating it and way off the mark so any help is greatly appreciated.

Here's an example of what I'm trying to do.

  ## Input/First table
  staff     minutes   type       
 --------- --------- ----------- 
  Bill      5         TELEPHONE  
  Bill      10        FACE2FACE  
  Bill      5         INDIRECT   
  Bill      5         EMAIL      
  Bill      10        OTHER      
  Gary      10        TELEPHONE  
  Gary      5         EMAIL      
  Gary      5         OTHER      
  Madison   20        FACE2FACE  
  Madison   5         INDIRECT   
  Madison   15        EMAIL      
  Rob       5         FACE2FACE  
  Rob       5         INDIRECT   
  Rob       20        TELEPHONE  
  Rob       45        FACE2FACE 


  ## Second table with SUM of minutes, Grand Total is column total.
  Row Labels    EMAIL   FACE2FACE   INDIRECT   OTHER   TELEPHONE  
 ------------- ------- ----------- ---------- ------- ----------- 
  Bill          5       10          5          10      5          
  Gary          5                              5       10         
  Madison       15      20          5                             
  Rob                   50          5                  20         
  Grand Total   25      80          15         15      35 


  ## Third table where each of the above cells is divided by its column total.
  Row Labels    EMAIL   FACE2FACE   INDIRECT      OTHER         TELEPHONE    
 ------------- ------- ----------- ------------- ------------- ------------- 
  Bill          0.2     0.125       0.333333333   0.666666667   0.142857143  
  Gary          0.2     0           0             0.333333333   0.285714286  
  Madison       0.6     0.25        0.333333333   0             0            
  Rob           0       0.625       0.333333333   0             0.571428571  
  Grand Total   25      80          15            15            35      



  ## Final table with the sum of the rows in the third table divided by 5.
    staff     AVERAGE      
   --------- ------------- 
   Bill      29.35714286  
   Gary      16.38095238  
   Madison   23.66666667  
   Rob       30.5952381

Please let me know if I can clarify an aspect.

Can you please make an example of your input data and your expected result. — Aldert
@Aldert, thanks, I've added an example in the original description. — verstehenmonk

vignesh subramanian vignesh subramanian · Accepted Answer · 2019-09-17T12:35:23

You can make use of the built in functions like %Row total in Power BI, Please find the snapshot below

If this is not what you are looking for, kindly let me know (I have used your Input table)

Average of percent of column totals in DAX

1 Answers