Value distributed to other Dimensions

[Anonymous]

Hello all

I hope you can help me. 🙏

I have a case where I need to compare used time from a fact-table (invoice) with a static estimated time that is located in another table (KIT-tabel).

The estimated time have a dimension called KIT no., which also relates to some of the rows within the fact-tabel, but the used time doesn't have a KIT-dimension (it is blank).

The used time also needs to be recalculated based on the time efficiency of the role of the user.

How can I distribute the recalculated used time on the KIT no. rows based on the KIT no.'s share of the invoices estimated time?

Here are the tables and the last picture is the outcome I hope for:

Can anyone help me on which way to go to solve this?

I have attached a PBIX-file as well, which can be found here: https://we.tl/t-7gPh0uJH5f

Thank you in advance! 😃

[Anonymous]

Hello @Anonymous 

Unable to download the file. Seems like the link/transfer is expired. Could you please share again?
Thank You.

[technolog · Accepted solution]

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 )