Allocation percentage of margin in different office

[ChrisLoan]

Hi everyone

I'm dealing with a special situation.

We have a Fact table BrutoMargin (Margin) where we have our margin numbers. The margin number has an office key that indicates where the margin is located. But there is one exception, when the margin deals with a number that has to do with an international worker, that's when 75% of the margin number needs to be allocated to the "International Office Key".

This is the datamodel situation right now, there are many relationships but the most important one is the relationship with the dimension table "Office":

Just to be clear this is a simplified version of what one row in the fact margin table would look like:

Office key -12 = Office local 1                              International office key -20 = International Office 1

The office key is joined with the dimension table "Office". I haven't done anything with "International office key" yet, but the key belongs to the same dimension table "office" to reference it's value.

My question is, how do I dynamically allocate 25% of the total margin number (625 euro) to the normal office (office key) ) and 75% (1875 euro) tot the "International office" (international office key), so that I can show this in a matrix table in Power BI.

We're using a simple Matrix table in Power BI to visualize the margin with the office and next to it the Margin.

The result I would like to see is this in a Matrix table Power BI:

Thanks!

[Sahir_Maharaj]

Hello @ChrisLoan,

Can you perhaps please try creating a DAX measure for the allocated margin:

Allocated Margin = 
VAR NormalOfficeKey = SELECTEDVALUE('Fact BrutoMarge'[Office_Key])
VAR InternationalOfficeKey = SELECTEDVALUE('Fact BrutoMarge'[International Office Key])
VAR TotalMargin = SUM('Fact BrutoMarge'[Margin])

RETURN 
    SWITCH(
        TRUE(),
        NormalOfficeKey <> BLANK(), TotalMargin * 0.25,
        InternationalOfficeKey <> BLANK(), TotalMargin * 0.75,
        BLANK()
    )

[ChrisLoan]

Sahir,

Have you had a chance to look at my reply underneath? It's really urgent.

Thanks.

[ChrisLoan]

Hi Sahir

Let's start again cause I forgot a few things 😁 (the percentage allocation can also go the other way around and is a variable column).

I'll give you all the info using a dummy table (one you can replicate in BI) and the result I want.

I've made a dummy table that looks like this:

Which gives me this result when I simply calculate the total margin (this is without the allocation stuff):

This is the result with your measure:

But this is the result I want (I hardcoded this to get the result):

• (office key -110) ANTN = total of 500 (25% of 2000 = 500)
• (office key -105) GENT = total of 2000 (100% of 250 + 25% of 3000 + 25% of 4000)
• (office key -36) IDM = total of 6750 (75% of 2000 + 75% of 3000 + 75% of 4000)

How do I do this? 🤔

Thank you so much in advance!

[ChrisLoan]

Hi Sahir

Thanks for the quick response! 😊

Do I need to connect the "International office key" in a relationship to the Dimension table "Dim Office" before I try this measure?

[v-jialongy-msft]

Hi @ChrisLoan 

Please try the following dax:

Adjusted Margin =
SUMX (
    BrutoMargin,
    VAR CurrentOfficeKey = SELECTEDVALUE ( Office[Office Key] )
    VAR IntlOfficeKey = BrutoMargin[International Office Key]
    VAR MarginValue = BrutoMargin[Margin]
    RETURN
        IF (
            NOT ISBLANK ( IntlOfficeKey ),
            SWITCH (
                TRUE (),
                CurrentOfficeKey = BrutoMargin[Office Key], MarginValue * 0.25,
                CurrentOfficeKey = IntlOfficeKey, MarginValue * 0.75,
                0
            ),
            IF ( CurrentOfficeKey = BrutoMargin[Office Key], MarginValue, 0 )
        )
)

If the problem persists, please provide sample data that fully covers your issue (in the form of table or pbix file) and the expected outcome based on the sample data you provided. Please remove any sensitive data in advance.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ChrisLoan]

Hi Jayleny,

Have you had a chance to look at my reply underneath? It's really urgent.

Thanks.

[ChrisLoan]

Hi Jayleny

Let's start again cause I forgot a few things! (the percentage allocation can also go the other way around and is a variable column).

I'll give you all the info using a dummy table (one you can replicate in BI) and the result I want.

I've made a dummy table that looks like this:

Which gives me this result when I simply calculate the total margin (this is without the allocation stuff):

This is the result with your measure:

But this is the result I want (I hardcoded this to get the result):

• (office key -110) ANTN = total of 500 (25% of 2000 = 500)
• (office key -105) GENT = total of 2000 (100% of 250 + 25% of 3000 + 25% of 4000)
• (office key -36) IDM = total of 6750 (75% of 2000 + 75% of 3000 + 75% of 4000)

How do I do this?

Thank you so much in advance!

[ChrisLoan]

Hi Jayleny

Thanks, I'm gonna give this a try as soon as possible and I will get back to you if I need more help 😊

One question though, do I need to connect the "International office key" in a relationship to the Dimension table "Dim Office" before I try this measure?

Thanks.