Forum Discussion
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?
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
4 Replies
- amitchandak
Super User
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.
- mahoneypat
Microsoft Employee
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
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.
