Forum Discussion
Need Help! Writing a Measure to multiply between two Fact Tables
Super UserAnonymous , These are the way you can work upon
What you need common dimension or dimesnion where you can multiply
In case you need one dimension
sumx(summarize(Dim, Dim[Dim], "_1", [Measure from Table1], "_2", [Measure from Tabl2]),[_1]*[_2])
In case you need more tha one dimensions
sumx(summarize(Table1,Dim[Dim], Dim2[Dim2], "_1", [Measure from Table1], "_2", [Measure from Tabl2]),[_1]*[_2])
amitchandak I'm not sure I follow what you're suggesting. Are you saying to use Summarize to calculate each measure and multiply the two results? In another Forum someone suggested using CROSSFILTER, however while that results in the mathematical operation properly occuring, it negates the filtering in the [Area by Care Setting Ratio] which results in the wrong amount of area being calculated.
Anyone....?
amitchandak I studied your proposed suggested a bit more and also used Performance Analyzer to peak at the underlying queries for the Table visuals I have. The problem I see is that each Measure has to be uniquely summarized on a different table, then the results have to be multiplied by each other. Therefor there is no single table that you can summarize on. This is illustrated in particular if you highlight a row and apply cross filtering in a series of tables visuals. See the images below. The table in the bottom left represents the aggregation of the two different measures and you'll note that the last column "Reccomended Area" is calculating "something" but its wrong. If I cross highlight different rows, you'll observe the changes in the seperate tables to the left and top, illustrating that the two measures are summarizing on different values.
Area By CategoryOverall Flow% of Benchmark ProgramIf it helps at all, when the current calculation is performed, it is using the Sum of all area by Category to mutliply by the %. So rather than using only the Sum of Area for Site 1 Acute Care, it Sums the Acute Care area from Site 1 and Site 2, then multiplies by the appropriate % (for each site). Since everything is wrapped in calculate statements, filtering the visual does not change the outcome. Maybe I need to add "Site" to [Area by Care Setting Ratio]?
Area by Care Setting Ratio = VAR ProgramArea = CALCULATE( [RT Project Area by Cat], FILTER( ProjectArea, RELATED(Categories[Dependent]) = FALSE() ), FILTER( ProjectArea, RELATED(Categories[CareServices]) = TRUE() ) ) RETURN SUM(Ratios[Percentage]) * ProgramArea