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

Cannot get desired Matrix visualization subtotals and totals

I'm working on building a matrix visualization showing employee time expenditures against assigned contract hours. For instance, Jennifer Smith has been assigned hours across three contracts. Each day she spends some number of hours working on each contract. I'd like to see running totals for the number of hours spent and the number of hours remaining (assigned - spent).

 

To implement this visualization, I have a table containing timecard data where each row shows the number of hours spent that day against a specific contract. In addition, I have a table containing assigned hours for each employee and each contract. I have tried merging these tables together, giving me a single table with a row containing hours spent and hours assigned for each employee and day, but the result is not as desired.

 

For instance, if we look at Jennifer Smith, we see the below. She’s not assigned 50 hours for the Alpha contract, though. She’s assigned 25, but when the two tables are merged the assigned hours appears in every row. This occurs for each contract: Beta she is assigned 20, not 80. Delta she is assigned 40, not 160.

 

 

Sample POC.png

 

I have uploaded an Power BI page with sample data here: Power BI Sample. Page 3 contains my most recent attempt at producing the desired output.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: Cannot get desired Matrix visualization subtotals and totals

@AdamOrmond

 

Hi, review the Attached PBIX Page 4. Is very close of the expected result.

 

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
Vvelarde Super Contributor
Super Contributor

Re: Cannot get desired Matrix visualization subtotals and totals

@AdamOrmond

 

Hi, try with this measure:

 

M-ContractHours.Assigned Hours =
IF (
    HASONEVALUE ( TimeCardContractHoursMerged[Date] ),
    BLANK (),
    AVERAGE ( TimeCardContractHoursMerged[ContractHours.Assigned Hours] )
)

Regards

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




AdamOrmond Frequent Visitor
Frequent Visitor

Re: Cannot get desired Matrix visualization subtotals and totals

Unfortunately that does not produce the desired output. It does show the average value for the day, 25 for Jennifer/Alpha, but the total values for the employee and across all employees do not add up. This is the desired result looks something like this for Jennifer Smith:

 

Employee Hours WorkedAssigned Hours
Jennifer Smith 11585
Alpha 1825
 Friday, May 410 
 Saturday, May 58 
Beta 3220
 Monday, May 77 
 Monday, May 86 
 Monday, May 911 
Delta 3340

 

 

Vvelarde Super Contributor
Super Contributor

Re: Cannot get desired Matrix visualization subtotals and totals

@AdamOrmond

 

Hi, review the Attached PBIX Page 4. Is very close of the expected result.

 

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




AdamOrmond Frequent Visitor
Frequent Visitor

Re: Cannot get desired Matrix visualization subtotals and totals

Thanks, Vvelarde! The TREATAS function was what I needed.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 133 members 1,672 guests
Please welcome our newest community members: