How to sum value from different tables based on page filters

[Anonymous]

I have dropdown slicers for Id, Sample Rate (Weekly vs Monthly), and Timestamp.

I also have two Fact tables with managed relationships to the above. Everything works as expected in all visuals when changing values in the dropdown slicers.

In both Fact tables, I've created to calculated columns: TotalWorkflowsControl &TotalWorkflowsTreatment.

Both tables have the same schema.

The goal is to create a calculated column which calculates each table's percentage of a column from both tables. The issue that when trying to calculate both columns, only the SUMX using a column from the same table is doing proper filtering, Id & Timestamp: (See below)

The top table below pulls its values from the table Fact_AS_Control_Metrics, the bottom from Fact_AS_Treatment_Metrics .

The defintion of the calculated columns in both tables are:

TotalWorkflowsControl = CALCULATE(SUM(Fact_AS_Control_Metrics[Workflows]))

TotalWorkflowsTreatment = CALCULATE(SUM(Fact_AS_Treatment_Metrics [Workflows]))

In the table Fact_AS_Control_Metrics, only TotalWorkflowsControl is correct (filtering by both Id & Timestamp).

In the table Fact_AS_Treatment_Metrics , only TotalWorkflowsTreatment is correct (filtering by both Id & Timestamp).

The incorrect column values are only filtering by Timestamp. Therefore, we're seeing the summation of the field across all Ids.

 

The top table should have the value of 42 instead of 312, the bottom table should have the value of 39 where we see 263.

Looking above column values should match vertically as displayed in the screenshot. It appears that the incorrect value is filtering by Timestamp correctly but is not filtering by Id. So, in the bottom table in the screenshot above, if we sum over the Workflows column for all Ids, we get 263. The value of the selected Id, 14749, should be 39 as seen in the top table.

I'm not sure how to get the filtering by Product to work for the column from the outside table for each table's calculated column.

The main goal is to DIVIDE(local table workflows' column value, by the sum of the workflows columns from both tables).
So for Treatment Metrics above, something like: DIVIDE(39, (39+42))

Thanks!

[Anonymous · Accepted solution]

The root cause of my issue was that I chose to create a new "column" instead of "measure". 

[tamerj1]

Hi @Anonymous 
I can see there is not total under TotalWorkflowsControl and TotalWorkflowsTreatment in bothe tables. That means the columns are not summarized, in other words the power bi engine did not create implicit measures for these two columns. In this case the columns became part of the filter context. I would guess that if you summarize both columns in both sammary tables by sum (sumarize by sum) or if you add them as measures ( SUM ) or in the first place to cerate measures instead of the columns like 
TotalWorkflowsControlMeasure = SUM(Fact_AS_Control_Metrics[Workflows])
TotalWorkflowsMetricslMeasure = SUM(Fact_AS_Control_Metrics[Workflows])

Then the problem would be solved