Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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


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
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 :S 😞

 image.png

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 🙂

 

image.png

 

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

 

 

 

 

Nice one!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.