Complex Matrix - is this even possible??

[Anonymous]

Hi,

I'm trying to replicate an Excel matrix of data using Power BI. I'm very new to PowerBI and have probably bitten off more than I can chew as my first project, haha.

The data I'm trying to replicate is HSE data similar to the pic below:

Tar = Target; Act = Actual

Data for this table comes out of reports I have created in a company HSE system as *.csv files. I send these off to a SharePoint library and from there I am using PBI to grab them and reshape them. The *.csv files are broken up into:

- each Type (ie Type 1, Type 2, ...) is a separate file

- Events is a separate file

- People is a separate file, and

- Targets will be a separate file but isn't as yet.

To date, I've managed to clean the Types and Append these together, then using DAX do an 'Events' COUNTROWS against this one Appended dataset but that's as far as I gotten and have a few questions before I go any further ie:

- Is it possible that when I filter by 'Selected Month' I can show 'Selected Month' and 'YTD' values in the same matrix?

- Not matter how I filter, always show ALL People in the Names dataset and ALL Types?

- For certain Types (eg Type 1 & Type 2) NOT show Events data (because these aren't reqd measures)?

- Create in Matrix calculated column for 'Event target' based on YTD Actual * %  eg Name1 Type 3 'Event target' = 9 x 0.4 = 3.6

- conditional formatting through matrix of Target vs Actual

I realise that this is fairly complex but I'm keen to see if it can be done and whether I can do it. Any and all help greatly appreciated.

Cheers

[CheenuSing]

Hi @Anonymous ,

Can you post some data if it is not company confidential to google or one drive and share the link here.

Please also share the output you expect from the data.

Cheers

CheenuSing

[Anonymous]

@CheenuSing 

I have managed to create a matrix with most of what I need. I've dropped the idea of the "Selected Month" column headings and just running with YTD.

I was wondering if you knew a way to remove the circled (in RED) column headings if they have blank data? This would pretty much finish this project off.

Matrix - remove circled columns if blank

Cheers

[Anonymous]

@CheenuSing 

Thanks for your reply. I didn't know how to send files so I just worked on a solution myself.

I'm slowly getting there, which is encouraging. I've built a number of 'helper columns' inside my model and am using these to get most of what I need. When I'm back in the office I will post an image of where I'm up to. I still have a few things I'd like to solve that you may be a Le to assist with.

Cheers