Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Value distributed to other Dimensions

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! 😃

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Anonymous 

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

  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    11 months ago

    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 )