Forum Discussion
Need Help! Writing a Measure to multiply between two Fact Tables
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 Program
If 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