Calculation using measure from one table/matrix and subtotal from another.

[MNLegoman]

How would I go about calculating "Unique REJ" divided by subtotal of "Count of Obj Type" (aka "Total) for each person by year given that this data is from 2 different matrices each a separate table?

[mahoneypat · Accepted solution]

Since you have a M:M relationship between the Created By columns in your two tables, you could make a bridge table of unique CreatedBy values to make 1:M relationships to both tables.  Or, with no relationship between the tables you can use this measure in a table visual with the Created By column from your TaskDetails table.

NewMeasure =
VAR vREJ = [Unique REJ]
VAR vTotalObjects =
    CALCULATE (
        COUNT ( CFECODetails[Obj Type] ),
        TREATAS (
            VALUES ( CFTaskDetails[Created by] ),
            CFECODetails[Created by]
        )
    )
RETURN
    DIVIDE (
        vREJ,
        vTotalObjects
    )

If you are going to do a lot more with this model, you should consider making the bridge table to make your future analyses/visualizations easier.  There are lots of articles/videos on making bridge tables out there.

Pat

[MNLegoman]

Thank you Pat! Your measure worked perfectly. I'll also take a look at the bridge table idea. That might prove useful at a later date.

[amitchandak]

@MNLegoman , can share the measure formula

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[MNLegoman]

Hope this is good. I had to cut the data way back, but I think there is still enough there to work with.

(link removed)