Calculate Ratios between two tables

[jcc3508]

Greetings,

I have three tables, as per the attached worksheets:

Table 1: "Sales" 

ID	Date	Product	Sales
455	3/15/2023	Apple	450
455	3/15/2023	Orange	200
455	3/15/2023	Pencil	178
455	4/15/2023	Apple	900
455	4/15/2023	Orange	178
455	4/15/2023	Pencil	205
455	6/15/2023	Apple	800
455	6/15/2023	Orange	700
455	6/15/2023	Pencil	956
123	1/10/2022	Apple	111
123	1/10/2022	Orange	222
123	1/10/2022	Pencil	333
123	4/15/2022	Apple	444
123	4/15/2022	Orange	555
123	4/15/2022	Pencil	666

Table 2: "Flows"

ID	Date	Item	Value
455	3/15/2023	Turnover	470
455	3/15/2023	Cost	204
455	3/15/2023	Labor	176
455	4/15/2023	Turnover	165
455	4/15/2023	Cost	431
455	4/15/2023	Labor	295
455	6/15/2023	Turnover	156
455	6/15/2023	Cost	161
455	6/15/2023	Labor	147
123	1/10/2022	Turnover	489
123	1/10/2022	Cost	371
123	1/10/2022	Labor	277
123	4/15/2022	Turnover	359
123	4/15/2022	Cost	426
123	4/15/2022	Labor	365

Table 3: "Settings"

ID	Projection	Base Date	User
455	Up	12/29/2022	ADY
123	Down	11/29/2022	KQR
367	Up	12/5/2022	KQR
498	Base	1/7/2023	KQR
958	Base	4/5/2023	KQR
447	Base	7/6/2023	ADY

Sales is related to Settings (many to one), via a column named "ID"

Flows is related to Settings (many to one), via a column named "ID"

Sales and Flows do not have an active relationship, and aside from having the "ID" column, they also share a "Date" column.

I am filtering a dashboard, by the "ID" column, showing only information for a specific "ID" at a time.

I would like to calculate Ratios between the "Sales" table and the "Flows" table for a vector of dates, given the "ID" that I am using. E.g.: for ID = "455"

Ratio	3/15/2023	4/15/2023	6/15/2023
Apple/Turnover	0.96	5.45	5.13
Orange/Cost	0.98	0.41	4.35

Could anyone help on how to do this? Thanks a lot.

[Anonymous · Accepted solution]

Hi @jcc3508 

You can refer to the following solution.

Step 1: You need to add index columns in sale table and flows table

You can create two blank query in power query and put the following codes to advanced editor in power query. then apply them.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddDNCoMwDMDxd+lZaJMmVY97gu0uHsYQEURk739YFcwiJKd+8KN/0mEIxByakCNwxIS57h/7vk51JU5hbCzx/L63+SCYPPKats+y1g20nSJkdPrbI2R1/Eekg4kVKUanu3WK1WldIp2ey0ngvIYI6SCoOgDgiP+/1aNNpJNzVuQaWXeIyBHSYWaHSKeUOs/4Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Product = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Product", type text}, {"Sales", Int64.Type}}),
    Custom1 = Table.Group(#"Changed Type", {"Date"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"ID", "Product", "Sales", "Index"}, {"ID", "Product", "Sales", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data",{{"Index", Int64.Type}})
in
    #"Changed Type1"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddC7CsMgFIDhd3EO6Lla564ds4UMLXStkF6evyZokJAzKfLhzznT5FjEDY48iMeAVO7jd3nl33MpV47BzcMZuub3pxwY2AC3+yOvX0DUTvB5B1QMVDtMYIDWwdR/oUZH1EC1AwoG2OfhuAnYXsFDWAUe9nZJBqodimCAfZ7Yd9rEhw5JMlDbG6oBWofW1c9/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Item = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Item", type text}, {"Value", Int64.Type}}),
    Custom1 = Table.Group(#"Changed Type", {"Date"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"ID", "Item", "Value", "Index"}, {"ID", "Item", "Value", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data",{{"Index", Int64.Type}})
in
    #"Changed Type1"

Step 2: You need to create two tables: Calendar and Ratio

Calendar

Calendar = CALENDAR(DATE(2022,1,1),DATE(2023,12,31))

Ratio

Ratio = SUMMARIZE(Sales,[Index],[Product],"Ratio",[Product]&"/"&LOOKUPVALUE(Flows[Item],[Index],Sales[Index]))

Step 3 Create relationship among tables.

Calendar[Date]->Sales[Date](1:n)

Calendar[Date]->Flows[Date](1:n)

Ratio[Index]->Sales[Index](1:n)

Ratio[Index]->Flows[Index](1:n)

Step 4 Create a measure

Radio % = var sum_sales=CALCULATE(SUM(Sales[Sales]))
var sum_flow=CALCULATE(SUM(Flows[Value]))
return DIVIDE(sum_sales,sum_flow)

Step 5 Create a matrix visual, Put the Radion fieldof Radion table  to row , Date field of calendar table to the colum , then put the mesaure to the value.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.