Forum Discussion
Value distributed to other Dimensions
Below is a clean plain text answer you can paste as is. No code blocks. No dashes. Extra line breaks separate sections. Formulas are on their own lines.
Model
Fact table columns
Date, Invoice, Kit no, Item, Used Time, User
KIT table columns
Kit No, Work Type, Estimated Time
Users table columns
User, Role
Roles table columns
Role, TimeEfficiency
Senior equals 1
Junior equals 0.5
Intern equals 0.25
Relationships
Users[User] to Fact[User]
Roles[Role] to Users[Role]
KIT[Kit No] to Fact[Kit no]
Keep single direction relationships. We will rely on TREATAS in measures.
Measures
Used Time
SUM ( Fact[Used Time] )
Used Time multiplied by TimeEfficiency
SUMX (
Fact,
Fact[Used Time] *
COALESCE (
LOOKUPVALUE (
Roles[TimeEfficiency],
Roles[Role],
LOOKUPVALUE ( Users[Role], Users[User], Fact[User] )
),
1
)
)
Estimated Time for the current KIT within the current invoice
VAR k = SELECTEDVALUE ( Fact[Kit no] )
RETURN
IF (
NOT ISBLANK ( k ),
CALCULATE (
SUM ( KIT[Estimated Time] ),
TREATAS ( { k }, KIT[Kit No] )
)
)
Estimated Time for the whole invoice
VAR KitsInInvoice =
CALCULATETABLE (
DISTINCT ( Fact[Kit no] ),
Fact[Kit no] <> BLANK (),
ALL ( Fact[Kit no] )
)
RETURN
CALCULATE (
SUM ( KIT[Estimated Time] ),
TREATAS ( KitsInInvoice, KIT[Kit No] )
)
Used Time multiplied by TimeEfficiency distributed by Estimated Time
VAR EffAtInvoice =
CALCULATE ( [Used Time multiplied by TimeEfficiency], ALL ( Fact[Kit no] ) )
VAR EstThisKit = [Estimated Time]
VAR EstInvoice = [Estimated Time for the whole invoice]
RETURN
EstThisKit * DIVIDE ( EffAtInvoice, EstInvoice )