Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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]

View solution in original post

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

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.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

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.

Anonymous
Not applicable

Thank you Maggie!!

Anonymous
Not applicable

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]

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.