Need help with some measures

[timknox]

I have a very large flat data file (300,000 lines and 25 columns), and a sample of the data would be:

Item	Code	01/11/2018	01/12/2018	01/01/2019	01/02/2019	01/03/2019
1.1	BDA	256	25	32	154	21
1.2	BDD	18	22	108	15	60
1.3	BCC	16	75	10	42	60
1.1	BDA	53	7	29	42	60
1.1	BDA	5	21	29	42	60
1.4	BDC	14	21	29	42	60
1.3	BCC	14	21	29	42	60
1.1	Vulnerability	15	8	5	9	12
1.2	Vulnerability	14	5	9	6	3

I am trying to create a measure that will sumarize the data by CODE, including any associated 'Vulnerability' linked by the 'Item'.

So for example if I have a slicer as shown (which does not include 'Vulnerability' as it is not a unique Code):

Code

BDA

BDD

BCC

BDC

And i click on BDA, i get the following results on one report page:

Item	Code	01/11/2018	01/12/2018	01/01/2019	01/02/2019	01/03/2019
1.1	BDA	314	53	91	238	141

Item	Code	01/11/2018	01/12/2018	01/01/2019	01/02/2019	01/03/2019
1.1	Vulnerability	15	8	5	9	12

Item	Code	01/11/2018	01/12/2018	01/01/2019	01/02/2019	01/03/2019
1.1	TOTAL	329	61	96	247	153

 I think i can achieve the first Matrix, but I am not sure how to achieve the other 2, including how to get the words 'Vulnerability' and 'Total' as shown.

Really appreciate any help.

Many thanks

[Anonymous · Accepted solution]

My recommendation is to split this data into 2 separate tables.  In the Edit Queries section, keep your current query as is, but add another step to unpivot the data.  This should get it into the format of Item, Code, Date, Value.

This query should now be set to "Enable Load" as off.

Next create 2 new queries that reference your original query.  Filter these tables such that vulnerability is in 1 table, and the codes are in the other.

Overall, every data point should exist in 1 table or the other.

Now, in the relationships area of power bi, link the tables together via the Item field.  From here you should be able to build your matrix showing the Code and its values, as well as the vulerabilities.

If you run into some troubles with the dates, i'd recommend a date table linked to both of your existing tables.