cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sam4u Frequent Visitor
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 Smiley Sad

Here is the scenario. I have sample tables:


image.png

 

and the second table (this is pretty static table values never change in this table) 

image.png

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:

 

image.png

 

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

image.png

 

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 Smiley Frustrated Smiley Sad

 image.png

I wish I knew DAX a little better .. Smiley Frustrated

 

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 Smiley Happy

 

image.png

 

smoupre - Thank yuo so much for pointing me into the right direction Smiley Happy

 

 

 

 

Highlighted
Super User
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!