How to have both total and average

[jhurst]

I'm a Power BI newbie & hope someone can help. I have a table visualization (I've also tried matrix as well) & want to have BOTH a Total AND Average at the bottom (like the picture below).  I've gotten one or the other but not both at the same time.  Is this possible?

[jdbuchanan71]

@jhurst 

You could do something like this where you check if you are on a reqular row (a single date) and if not return both the total and the average.

COUNT = 
VAR _avg = AVERAGEX ( VALUES ( 'Date'[Date] ), [Count Measure] )
VAR _count = [Count Measure]
RETURN
    IF (
        HASONEVALUE ( 'Date'[Date] ),
        _count,
        FORMAT ( _count, "#,#" ) & " (" & FORMAT ( _avg, "#,#" & ")" )
    )

[jdbuchanan71 · Accepted solution]

Actually, building on my previous reply, you can put a line break in a measure with a shift + enter inside a text string.

Then you get something like this which is close

You do have to turn on Word wrap for the values field as well in order for the line break to show up.

[jhurst]

@ jdbuchanan71

So this code is a measure, right? (I did say I was new to this)

When I enter it, then adjust for the names of my table & fields I get the message "The name 'Count' is already used for a column name on table 'Query1'.  Choose a different name."  Then when I rename it, it ends up adding a new column instead of just putting the total & average on the bottom of the existing column.

[jdbuchanan71]

Right, you can take your first column out though and only show the new one.

[jhurst]

So if I have multiple columns & wanted the total & average for each of them, I'd need to create multiple measures - one for each of them?

[jdbuchanan71]

That's correct.  

[jhurst]

@ jdbuchanan71

Maybe.  Is there any way to change the row name to "Total (Average)"?  Otherwise people wouldn't necessary know what that 2nd number was.

[jdbuchanan71]

Yes, you can change the total label in the formatting:

[amitchandak]

@jhurst , I doubt you can have an additional total row. You can have either avg or total.

[camargos88]

Hi @jhurst ,

I haven't seen a visual like this. However you can drag the same column again to the visual and change the aggregation in the fields pane.

[jhurst]

Unfortunately, it added a new column with the same data; not exactly what I was looking for.