Forum Discussion

MNLegoman's avatar
MNLegoman
Frequent Visitor
5 years ago
Solved

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

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's avatar
    5 years ago

    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