Forum Discussion
Dynamic Measures based on changing data
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.
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.
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.
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"
10 Replies
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-Forum/ba-p/963216
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/1447523Thank 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% 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.
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
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.
then automatically create a new measureYou 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.
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"
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.
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"