Calculate Ratios between two tables

Question

Greetings,&nbsp;I have three tables, as per the attached worksheets:&nbsp;Table 1: "Sales"&nbsp;&nbsp;IDDateProductSales4553/15/2023Apple4504553/15/2023Orange2004553/15/2023Pencil1784554/15/2023Apple9004554/15/2023Orange1784554/15/2023Pencil2054556/15/2023Apple8004556/15/2023Orange7004556/15/2023Pencil9561231/10/2022Apple1111231/10/2022Orange2221231/10/2022Pencil3331234/15/2022Apple4441234/15/2022Orange5551234/15/2022Pencil666&nbsp;&nbsp;Table 2: "Flows"&nbsp;IDDateItemValue4553/15/2023Turnover4704553/15/2023Cost2044553/15/2023Labor1764554/15/2023Turnover1654554/15/2023Cost4314554/15/2023Labor2954556/15/2023Turnover1564556/15/2023Cost1614556/15/2023Labor1471231/10/2022Turnover4891231/10/2022Cost3711231/10/2022Labor2771234/15/2022Turnover3591234/15/2022Cost4261234/15/2022Labor365&nbsp;Table 3: "Settings"&nbsp;IDProjectionBase DateUser455Up12/29/2022ADY123Down11/29/2022KQR367Up12/5/2022KQR498Base1/7/2023KQR958Base4/5/2023KQR447Base7/6/2023ADY&nbsp;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.&nbsp;I am filtering a dashboard, by the "ID" column, showing only information for a specific "ID" at a time.&nbsp;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"&nbsp;Ratio3/15/20234/15/20236/15/2023Apple/Turnover&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.96&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.45&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.13Orange/Cost&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.98&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0.41&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.35&nbsp;&nbsp;Could anyone help on how to do this? Thanks a lot.

anonymous · Accepted Answer

Hi&nbsp;jcc3508&nbsp;
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]&amp;"/"&amp;LOOKUPVALUE(Flows[Item],[Index],Sales[Index]))

&nbsp;
Step 3 Create relationship among tables.
Calendar[Date]-&gt;Sales[Date](1:n)
Calendar[Date]-&gt;Flows[Date](1:n)
Ratio[Index]-&gt;Sales[Index](1:n)
Ratio[Index]-&gt;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&nbsp; 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&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;

Forum Discussion

Calculate Ratios between two tables

1 Reply

Related content

Ratio Calculation

ratio calculation

Ratio Calculation

Calculate Dynamic Ratios

Calculating ratios in dax

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

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

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

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