Forum Discussion
Need help with creating a measure
So I have 2 table with many to many relationship (example below) and I am trying to multiply one field of table 1 to another field in table 2. As easy as it sounds, for some reason I cannot figure it out how to do it in PowerBi, even reading dozens on post on the forums :(
Here is the scenario. I have sample tables:
and the second table (this is pretty static table values never change in this table)
I need a measure that can handle the following logic: For each order in Table 1, based on Material type, find the corresponding accounts in table 2 and multiply the cost of the work order with % distribution
I.E. WO 1000001 has MAT type XYZ789; XYZ settles to 2 accounts 41500007 and 41500008, therefore the $10 amount would be distributed like 10*30% + 10*70%; or account 41500007 will have $3 from that WO and 41500008 will get $7
Similary for 1000005 ; account 41500023 will have 60%*$20 = $12 and account 41500024 will have 40%*$20=$8
In excel that can be easily done like with a summary table like this:
Now, my problem is how to create this in Power BI. I want to report the total amount for each account and idealy slice it per Mat type and count of WO contributing for this amount
let say chart(s) that will represent for account 41500008 I have $21 and 2 WO contributed; for account 41500023 I have $12 and 1 WO etc.
If Anyone can help me with creating a measure that will connect the 2 table and multiply Amount and % I'll greatly appreciated.
Hmm, I'm close but not quite there. Given:
Table WorkOrders let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAUMlHaWIyChzC0sgw9BAKVYHJmMEFHB0cjY0MgYyLJEkjJG1GCFrMUHWYowkYQrkB7j7mZoZQrXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WorkOrder = _t, MaterialType = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkOrder", Int64.Type}, {"MaterialType", type text}, {"Amount", Int64.Type}}) in #"Changed Type" Table MaterialTypes let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVtJRMjE0NQACQyDTUClWJ1opIjLK3MISIWMOZOoZY5WyAEmZg6UC3P1MzQzhUmCj9cywSpmApEyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MaterialType = _t, Account = _t, #"%Distribution" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"MaterialType", type text}, {"Account", Int64.Type}, {"%Distribution", type number}}) in #"Changed Type" Table Materials let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVaKiIwyt7AEMwPc/UzNDJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Materials = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Materials", type text}}) in #"Changed Type"With many-to-many via "Materials" columns, created measure:
MyAmount = SUM([Amount])*CALCULATE(SUM(MaterialTypes[%Distribution]),RELATEDTABLE(MaterialTypes))
Created Matrix visualization:
Rows: WorkOrder from WorkOrders table
Columns: Accounts from MaterialTypes table
Values: MyAmount measure
Close but returns a value when there is no relationship.
3 Replies
Hmm, I'm close but not quite there. Given:
Table WorkOrders let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAUMlHaWIyChzC0sgw9BAKVYHJmMEFHB0cjY0MgYyLJEkjJG1GCFrMUHWYowkYQrkB7j7mZoZQrXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WorkOrder = _t, MaterialType = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkOrder", Int64.Type}, {"MaterialType", type text}, {"Amount", Int64.Type}}) in #"Changed Type" Table MaterialTypes let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVtJRMjE0NQACQyDTUClWJ1opIjLK3MISIWMOZOoZY5WyAEmZg6UC3P1MzQzhUmCj9cywSpmApEyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MaterialType = _t, Account = _t, #"%Distribution" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"MaterialType", type text}, {"Account", Int64.Type}, {"%Distribution", type number}}) in #"Changed Type" Table Materials let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVaKiIwyt7AEMwPc/UzNDJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Materials = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Materials", type text}}) in #"Changed Type"With many-to-many via "Materials" columns, created measure:
MyAmount = SUM([Amount])*CALCULATE(SUM(MaterialTypes[%Distribution]),RELATEDTABLE(MaterialTypes))
Created Matrix visualization:
Rows: WorkOrder from WorkOrders table
Columns: Accounts from MaterialTypes table
Values: MyAmount measure
Close but returns a value when there is no relationship.
yep, it seems to just multiply the amount regardless it the given WO has the MAT or not. I.e when I do it, it gives me
Where 10, 6 and 4 should not exist... seems like the measure is missing a filter for the aplicable WO...
I can add count to it :
MyAmount2 = SUM(WorkOrders[Amount])*CALCULATE(SUM(MaterialTypes[%Distribution]),RELATEDTABLE(MaterialTypes))*CALCULATE(COUNT(WorkOrders[WorkOrder]),RELATEDTABLE(MaterialTypes))
but then the totals are totally out of wack :S :(
I wish I knew DAX a little better .. :S
Another Edit:
I think I got it to work:
MyAmount = CALCULATE(SUM(WorkOrders[Amount])*SUM(MaterialTypes[%Distribution])*IF(ISNUMBER(count(WorkOrders[WorkOrder])),1,0)/COUNTA(Materials[Materials]),RELATEDTABLE(MaterialTypes))
It is not very elegant but seems to get the job done :)
smoupre - Thank yuo so much for pointing me into the right direction :)
Nice one!
