cancel
Showing results for
Did you mean:
Sam4u Frequent Visitor

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: Need help with creating a measure

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

3 REPLIES 3 Super User

## Re: Need help with creating a measure

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Sam4u Frequent Visitor

## Re: Need help with creating a measure

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   I wish I knew DAX a little better .. 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 Highlighted Super User

## Re: Need help with creating a measure

Nice one!

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!