cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Saket_Casper Frequent Visitor
Frequent Visitor

Matrix Calculation - Filter

Hi there,

 

Here is a snippet of my data.

CompanyCategoryHoursUnique KeyWeekDenominator Hours
BrazilA40Brazil220181/13/201840
BrazilA32Brazil320181/20/201840
BrazilA40Brazil420181/27/201840
BrazilB8Brazil320181/20/201840
BrazilC40Brazil120181/6/20180
BrazilA43Brazil520182/3/201840
BrazilA40Brazil620182/10/201840
BrazilA34Brazil720182/17/201840
BrazilB6Brazil720182/17/201840
BrazilC40Brazil820182/24/20180


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
kexley Regular Visitor
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
Community Support Team

Re: Matrix Calculation - Filter

Hi @Saket_Casper 

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]))

Capture7.JPG

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
kexley Regular Visitor
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
Community Support Team

Re: Matrix Calculation - Filter

Hi @Saket_Casper 

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]))

Capture7.JPG

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.

Saket_Casper Frequent Visitor
Frequent Visitor

Re: Matrix Calculation - Filter

Thank you Maggie!!

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 127 members 1,747 guests
Please welcome our newest community members: