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 Program