cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Average per month in visual card

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.

Super User

@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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors