Average per month in visual card

[clim2f88j]

Hello everyone,

I need to show the data below in a table. There are 4 users (A,B,C,D) that worked cases between January and March.

User     January     February     March

A             5               3                 2 

B             4               2                 2

C            5               5                4

D           10              8                4

I need to add a column that shows how many cases everyone worked for that month and another colum for how many each user worked that year, like this:

User     January     February     March     Total Year

A                    5               3                 2              10

B                    4               2                 2              8

C                    5               5                4               14

D                  10              8                4               22

Total             24              18             12              54

And once that's done, then i need a visual card that shows the average per month for all users. So if January is selected then the card should show: 6

Power query doesn't let me add totals down a column. I tried to do the List.Sum but it adds across users instead of each month.

Thank you for your help!

[amitchandak]

@clim2f88j , Assume you already have measure for sum , M1

Create a measure

m2= averagex(summarize(Fact, Date[Month], Fact[Name]), [M1] )

You can use isinscope to switch the GT

example

if(isinscope(Date[Month]) , [M1] , [M2])

Avg of Sum: https://youtu.be/cN8AO3_vmlY?t=22980

How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA