Multiply Across Fact Tables with Measures

[Anonymous]

Hi all,

I have two Fact Tables, with a two measures that calculate values from each Fact Table. The Fact Tables share common Dimension Tables for the purposes of aggregation and filtering. As you can see in DAX studio (and in PowerBI) I can generate a table that calculates both measures properly in the context supplied by the Dimension Tables.

However, what I need to do is multiply the sum of the one measure (based on one of the dimensions) by the value calculated by the other measure. Help!

[lbendlin]

Use another measure (or do the computation inline) using the ALLEXCEPT('prog Space Categories','prog Space Categories'[Name]) filter modifier.

[Anonymous]

@lbendlin I tried a couple of different permutations but I'm clearly not following what you're suggesting, would you mind providing a slightly more detailed explanation of what you're thinking?

Thank you,

-Robert

[lbendlin]

Please provide sample data in usable format.

[Anonymous]

@lbendlin 

Sample PBIX and Source Data file can be downloaded from here.

Most of the terminology should match with the previous screen shot, there is a little varitation, and I simplified a couple of things. There are a couple of reference pages in the PBIX with tables on them and several relationship diagrams given there are actually three star schemas invovled.

Screen shot illustrating what I'm attempting to accomplish.

Thank you,

-Robert

[lbendlin]

Why is Subcategories4 not a child dimension of Categories?

[Anonymous]

Two reasons

(1) its not always defined, particularly in the Projects Data Model, you'll note that none of those rows have a relationship to Subcategory

(2) its not actually used for any of these calculations and analysis, however the original "Benchmark" data was defined at that granular level and when the project started it wasn't clear if the end user was going to want any reporting or analysis at the Subcategory level. So the data was retained.

In the real data model, there is hierarch for Category\Subcategory, but again more as a CYA since the rest of the model, visualzation and analysis is all focused on Category, Care Setting & Site.

[lbendlin]

is this a correct representation, with "dimensions" at the top and "facts" at the bottom ?

In light of all the field name changes, do you mind restating the original DAX Studio query with the new names?

[Anonymous]

The representation is correct. Yes, I can write the original query. Just not from this device at the moment.

[Anonymous]

Good news, bad news... I realized I can't re-create the original statement. It was relying on a relationship that I shouldn't rely on which was exposed thanks to my cleaned up example model.

Thinking about it further, originally I was thinking that I needed to summarize the ProjectArea table, but I don't think that is appopriate. There is no data I need to summerize from that table. I need to leverage the data, but not summarize it. I'm generating new data for each Depdent Category by mutiplying the measures [Area by Care Setting Ratio] * [% of Benchmark Dependent Program by CareSetting].

I need to iteratate through each Depdent Category multiplying those values, but the values have to have the proper context as in the visual tables in the sample file.

Thoughts?

[lbendlin]

It's always good to question and review the data model. This also helps to better identify what you (or your users) are actually trying to achieve, and what they plan to do with the results.

It's a process, it may not be pretty but I am confident it will result in progress.

I'll chew on your last post for a bit and will let you know if I have meaningful input.