cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Matrix Calculation - Filter

Hi there,

Here is a snippet of my data.

 Company Category Hours Unique Key Week Denominator Hours Brazil A 40 Brazil22018 1/13/2018 40 Brazil A 32 Brazil32018 1/20/2018 40 Brazil A 40 Brazil42018 1/27/2018 40 Brazil B 8 Brazil32018 1/20/2018 40 Brazil C 40 Brazil12018 1/6/2018 0 Brazil A 43 Brazil52018 2/3/2018 40 Brazil A 40 Brazil62018 2/10/2018 40 Brazil A 34 Brazil72018 2/17/2018 40 Brazil B 6 Brazil72018 2/17/2018 40 Brazil C 40 Brazil82018 2/24/2018 0

On Power BI matrix I am trying to calculate the monthly % of total hours to denominator hours by category for month. So that for category a in month of Jan the calculation shud be = Sum of total hours in month of jan i.e 112 hours / sum of total denominator hours in month of jan i.e 120.

Similary for category B the calculation should be 6.66% i.e 8hours /120hours.

Is there a way to do this?

Regards

2 ACCEPTED SOLUTIONS

Accepted Solutions
Regular Visitor

## Re: Matrix Calculation - Filter

Hi,

You'll need to either add a calculated column for the month, or connect the date to a Date Dimension table and use the month from there.

```Monthly Hours Pct =
CALCULATE (
SUM ( Table1[Hours] ) / 120,
ALLEXCEPT ( Table1, Table1[Month], Table1[Category] )
)```

To get a calculated column for the month you could use the following.

`Month = Table1[Date].[Month]`
Community Support Team

## Re: Matrix Calculation - Filter

Create a column

`year-month = FORMAT([Week],"yyyymm")`

Create measures

```de hrs per month = CALCULATE(SUM('Table'[Denominator Hours]),ALLEXCEPT('Table','Table'[year-month]))

hours per cate per month = CALCULATE(SUM('Table'[Hours]),ALLEXCEPT('Table','Table'[Category],'Table'[year-month]))```

to get monthly % of total hours to denominator hours by category for month,

Use [hours per cate per month]/[de hrs per month]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Regular Visitor

## Re: Matrix Calculation - Filter

Hi,

You'll need to either add a calculated column for the month, or connect the date to a Date Dimension table and use the month from there.

```Monthly Hours Pct =
CALCULATE (
SUM ( Table1[Hours] ) / 120,
ALLEXCEPT ( Table1, Table1[Month], Table1[Category] )
)```

To get a calculated column for the month you could use the following.

`Month = Table1[Date].[Month]`
Community Support Team

## Re: Matrix Calculation - Filter

Create a column

`year-month = FORMAT([Week],"yyyymm")`

Create measures

```de hrs per month = CALCULATE(SUM('Table'[Denominator Hours]),ALLEXCEPT('Table','Table'[year-month]))

hours per cate per month = CALCULATE(SUM('Table'[Hours]),ALLEXCEPT('Table','Table'[Category],'Table'[year-month]))```

to get monthly % of total hours to denominator hours by category for month,

Use [hours per cate per month]/[de hrs per month]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

## Re: Matrix Calculation - Filter

Thank you Maggie!!

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 127 members 1,747 guests
Recent signins: