creating matrix table help need with columns and relationship

[rssilvaba]

Hi All,

I am trying to create a matrix table visual.

The part that I am struggling with is the columns name that should be present there and the values. 
The layout shoud be the following:

projectname/requestid	assigned-created	completed-assigned
<whatever proj/req is filtered>	<value in hours>	<value in hours>
<whatever proj/req is filtered>	<value in hours>	<value in hours>
avg's	<avg>	<avg>

So here is what I have at the moment.

First table name 'history'. the important columns are:
request id: key mapping to the request.
event name: created, assigned,completed.
event date: dates of the above

second table:
id: request id,
project name: name of the proj that the request is associated to.


I can calculeted that datediffs . I jsut dont have any idea how to come up with the matrix. Any help will be very much appreciated.

[V-lianl-msft]

Hi @rssilvaba ,

I created the test data as you said, but I can't get the time difference. The data I created involves the problem of circular iteration.

Could you please share sample data or sample pbix?(Please mask any sensitive data before uploading)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rssilvaba]

Hi all,

Here some dummy data:

https://drive.google.com/open?id=1cSeA86bNvzaZtQ_PJlltQq7BTD4Psxl5

[V-lianl-msft]

Hi @rssilvaba ,

See if it is you expect.

Please refer to the pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mwegener]

Hi @rssilvaba ,

could you please remove the empty rows.

[rssilvaba]

Done, please download it again. I put what I could do with a table instead. But the goal is to have a row with the averages and if possible the grouping that matrix does for projectname>reqid

Thank you for the help everyone.

[mwegener · Accepted solution]

Hi @rssilvaba ,

check this out.

PBIX

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[rssilvaba]

Hi @mwegener<

Thanks for that, could you please let me know how you put the average in the total?

[rssilvaba]

HI @mwegener ,

Ok I see the measures now.

Thank you very much. I am accepting your reply.

[mwegener]

Hi @rssilvaba ,

could you provide some sample data?

I think you will use AVERAGEX, to aggregate the measures (assigned-created, completed-assigned).

https://docs.microsoft.com/en-us/dax/averagex-function-dax

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.