Need Help! Writing a Measure to multiply between two Fact Tables

[Anonymous]

I need to multiply two measures by each other. The challenge is the two measures are calculated against two different Fact Tables. The information in the two Fact Tables is indirectly related by a common Dimension Table. One measure represents a percentage, the other measure represents a Value calculated by multipling by another Percentage.

For each unique Value it needs to be multiplied by the appropriate Perecentage based on Site & Care Setting (Dimensions) to determine which Percentage to use to multiply the Value.

I've thought a great deal about this, I think maybe virtual tables are required, to pull all the data together, but it is escaping me.

The data models breaks into three clean star schemas with shared Dimension Tables. All one to many relationships with single direction filtering.

Help! 🙂

See screenshots below.
Sample data here.

Data ModelCalculation Flow Diagram

[amitchandak]

@Anonymous , 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])

[Anonymous]

@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.

[Anonymous]

Anyone....?

[Anonymous]

@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

[Anonymous]

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