Dynamic Measures based on changing data

[AlunThomas]

Hi there (my first forum post ever so hope i am in the right place). I am looking to create some dynamic measures but unclear how to assign them and would apprciate some help:

I have two tables:

1) "FinishedGoods" Table: A table of product sales qty and their total weights in [kg]

2) "MaterialMatrix" Table: A table of products with columns for their core material composition by [%]. The column header is the name of the material (Brass, Copper etc.). New materials can be added to the source data at any time without me knowing.

My objective is to establish the proportion of weight [kg] by material type for the products sold. Not all products contain all materials.

This is what have got working so far (i am new to Power BI - sorry):

- My data model sucks in an excel report to create the "MaterialMatrix" table for material (copper brass etc.).

- I then duplicate this table to create a new table called "Materials" which I transpose the headers into rows and then remove duplicates to obtain a complete list of material types which are arriving.

- I can establish the annual weight of a material manually by using: Measure(Brass) = sumx('FinishedGoods','FinishedGoods'[TotalWeight[kg]]]*RELATED(MaterialMatrix[Brass])) BUT this only works if i already knew the materials to search for. What if a new material arrives in the data set tommorrow?

What i now need is to create dynamic measures for each material on my "materials" table using code. 

How can i make dynamic measures based on unknown material types arriving in the source data please? Any help would be appreciated and thanks in advance.

[lbendlin · Accepted solution]

The process is called "unpivoting"  and it is usually done in Power Query during the ETL.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg9y9HMJcAxy9Qsx1IfTQf4uoc4hhko6SmaqQMLIAEQqALG5CYQVq4NfqxFQlaEFql4zI+L0GgNVmRiD9YJ1QJxgCdUcCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FamilyHelper = _t, ABS = _t, Aluminium = _t, NewPreviouslyUnknownMaterial1 = _t, NewPreviouslyUnknownMaterial2 = _t, #"Polyamide " = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"FamilyHelper"}, "Material", "Percentage")
in
    #"Unpivoted Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

That way you make the data usable for Power BI and you can accommodate any new columns in your source data.

[lbendlin · Accepted solution]

That's just the way your sample data is encoded. Has nothing to do with your ask.  The important part is to apply the unpivot step to your actual data.

[AlunThomas · Accepted solution]

For anyone who is as thick as me; the final code i used looked like this:

let
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(MaterialMatrix,{"FamilyHelper"},"Material", "Percentage")
in
#"Unpivoted Other Columns"

[AlunThomas]

Ibendlin - Thanks for your help!!!

I now realise (duh) that the example you sent me was referencing my example data. Changed it to my own data and works a treat. Thank you for your help - i appreciate it.

[AlunThomas]

So on day 1 the material_matrix table might arrive like this:

FamilyHelper	ABS	Aluminium	Polyamide
GRANDPARENT1/PARENT1/PRODUCT1	6%	20%	74%
GRANDPARENT1/PARENT1/PRODUCT2	18%	20%	62%
GRANDPARENT1/PARENT1/PRODUCT3	43%	22%	35%

and then on day 2 it might arrive like this (with new material "Wood"):

FamilyHelper	ABS	Aluminium	Polyamide	Wood
GRANDPARENT1/PARENT1/PRODUCT1	6%	20%	74%
GRANDPARENT1/PARENT1/PRODUCT2	18%	20%	62%
GRANDPARENT1/PARENT1/PRODUCT3	43%	22%	35%
GRANDPARENT1/PARENT1/PRODUCT4	12%	45%		43%

And then on day 3 it might arrive like this (with new material "Ceramic")

FamilyHelper	ABS	Aluminium	Polyamide	Wood	Ceramic
GRANDPARENT1/PARENT1/PRODUCT1	6%	20%	74%
GRANDPARENT1/PARENT1/PRODUCT2	18%	20%	62%
GRANDPARENT1/PARENT1/PRODUCT3	43%	22%	35%
GRANDPARENT1/PARENT1/PRODUCT4	12%	45%		43%
GRANDPARENT1/PARENT1/PRODUCT5	45%			19%	36%

..... and tomorrow, might be a host of new materials not previously hard coded (which i wont have foresight of until the data in the materials_matrix arrives on a refresh).

So i am looking for PowerBI to detect the new material column in the material_matrix incoming data and then automatically create a new measure that reports on this new material % weight in the same format as the ones i showed in the previous examples (image).

This may not be possible - thanks in advance.

[lbendlin]

then automatically create a new measure 

You don't need to do that.  You can keep using the same measure, regardless of the material. That's the point of the unpivoting exercise.

[AlunThomas]

ok - i understand (thank you): but the code is still not bringing in the new column materials into Power BI and is only showing the original ones i included in the example table i provided. Sorry for the stupid question, but the code below does appear to have specific materials hard-coded (bold below). Should i be replacing this bit with something else?

Also, out of interest, why is Polyamide the only one in inverted commas?

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg9y9HMJcAxy9Qsx1IfTQf4uoc4hhko6SmaqQMLIAEQqALG5CYQVq4NfqxFQlaEFql4zI+L0GgNVmRiD9YJ1QJxgCdUcCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FamilyHelper = _t, ABS = _t, Aluminium = _t, NewPreviouslyUnknownMaterial1 = _t, NewPreviouslyUnknownMaterial2 = _t, #"Polyamide " = _t]),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"FamilyHelper"}, "Material", "Percentage")

in

    #"Unpivoted Other Columns"

[lbendlin · Accepted solution]

That's just the way your sample data is encoded. Has nothing to do with your ask.  The important part is to apply the unpivot step to your actual data.

[AlunThomas]

Thank you Ibendlin - my first ever forum post so please bear with me :-).

I followed the guidance, but my company is blocking external link sharing on OneDrive, so i placed the information in tables below and included a screenshot of the final outcome i am trying to achieve. I am looking for the two columns "NewPreviouslyUnknownMaterial1" and "NewPreviouslyUnknownMaterial2" to be created automatically when a new material column is detected in in the "material_matrix" incoming data:

Example measure i am creating manually (this is the item i need help with; the data set can arrive with new materials on which may not have previously been converted to measures. I am looking for a way to automatically create new measures based on new materials):

Measure (NewPreviouslyUnknownMaterial1) = sumx('Finished Goods','Finished Goods'[Total Weight]*RELATED(MaterialMatrix[NewPreviouslyUnknownMaterial1]))

Example "finished_goods" Table:

FamilyHelper	Date	Qty Total	Weight	Total Weight
GRANDPARENT1/PARENT1/PRODUCT1	12/10/2022	11,291	3.000	33873
GRANDPARENT1/PARENT1/PRODUCT2	12/10/2022	10,944	3.000	32832
GRANDPARENT1/PARENT1/PRODUCT3	12/10/2022	10,844	3.000	32532
GRANDPARENT1/PARENT1/PRODUCT1	20/12/2022	11,291	3.000	33873

Example "material_matrix" table:

FamilyHelper	ABS	Aluminium	NewPreviouslyUnknownMaterial1	NewPreviouslyUnknownMaterial2	Polyamide
GRANDPARENT1/PARENT1/PRODUCT1	6%	20%		74%
GRANDPARENT1/PARENT1/PRODUCT2	18%	20%		62%
GRANDPARENT1/PARENT1/PRODUCT3	43%	22%	6%	29%

[lbendlin · Accepted solution]

The process is called "unpivoting"  and it is usually done in Power Query during the ETL.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg9y9HMJcAxy9Qsx1IfTQf4uoc4hhko6SmaqQMLIAEQqALG5CYQVq4NfqxFQlaEFql4zI+L0GgNVmRiD9YJ1QJxgCdUcCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FamilyHelper = _t, ABS = _t, Aluminium = _t, NewPreviouslyUnknownMaterial1 = _t, NewPreviouslyUnknownMaterial2 = _t, #"Polyamide " = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"FamilyHelper"}, "Material", "Percentage")
in
    #"Unpivoted Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

That way you make the data usable for Power BI and you can accommodate any new columns in your source data.

[AlunThomas]

Firstly - i really appreciate your help and expertise lbendlin.

I have just the query using the code above and unfortunatly that has not worked. There are many more material columns than the 5 examples i provided (ABS, Aluminium, NewPreviouslyUnknownMaterial1, NewPreviouslyUnknownMaterial2, Polyamide) and it didnt bring any of these new columns into the data. 

As a reminder (im sure i didnt explain myself well); my objective is for Power BI to automatically detect a new material column that a user might have entered into the material_matrix table when the data is refreshed and then create a new measure for this material which i can then report on. So, when i refresh the data tommorrow, and there is a new material in the list i have not previuosly seen (say Copper), i want Power BI to recognise there is a new column, create a new measure and then report on the new material % by weight.

What am i doing wrong (output below):

FamilyHelperMaterialPercentage

GRANDPARENT1/PARENT1/PRODUCT1	ABS	6%
GRANDPARENT1/PARENT1/PRODUCT1	Aluminium	20%
GRANDPARENT1/PARENT1/PRODUCT1	NewPreviouslyUnknownMaterial1
GRANDPARENT1/PARENT1/PRODUCT1	NewPreviouslyUnknownMaterial2	74%
GRANDPARENT1/PARENT1/PRODUCT1	Polyamide
GRANDPARENT1/PARENT1/PRODUCT2	ABS	18%
GRANDPARENT1/PARENT1/PRODUCT2	Aluminium	20%
GRANDPARENT1/PARENT1/PRODUCT2	NewPreviouslyUnknownMaterial1
GRANDPARENT1/PARENT1/PRODUCT2	NewPreviouslyUnknownMaterial2	62%
GRANDPARENT1/PARENT1/PRODUCT2	Polyamide
GRANDPARENT1/PARENT1/PRODUCT3	ABS	43%
GRANDPARENT1/PARENT1/PRODUCT3	Aluminium	22%
GRANDPARENT1/PARENT1/PRODUCT3	NewPreviouslyUnknownMaterial1	6%
GRANDPARENT1/PARENT1/PRODUCT3	NewPreviouslyUnknownMaterial2	29%
GRANDPARENT1/PARENT1/PRODUCT3	Polyamide

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...