How to assign values for one Business division to another

[James_Ma]

Hi,

Can anyone help me with this request please?

I have a table visual in PowerBI where it shows the "Amount for Supplier costs" for 4 separate Business Areas.

i.e. PI = £2,971,586.04, TR = £122,591.57 fianl result should show PI = £0 and TR = £3,094,177.61

Charge Type	Amount for Supplier costs	Supplier	Business Area	Internal/External
Supplier costs	£202,789.82	HomeRef	Corporate	Interna
Supplier costs	£0.00	HomeRef	IN	Internal
Supplier costs	£2,971,586.04	HomeRef	PI	Internal
Supplier costs	£122,591.57	HomeRef	TR	Internal

There is a measure used to calculate the Amount for Supplier costs:

Amount for Supplier costs =

CALCULATE(

    SUM('billing'[Amount]),

    'billing'[ChargeType]

        IN { "Supplier costs" }

)

[James_Ma · Accepted solution]

Just looked at this again and added the Business area from the case table and  all seemed good, but PI has 0 values for internal and external, so ideally I need to add to the measure to only do this calc if the internal external column is internal.

Measure = SWITCH(SELECTEDVALUE('Case'[Business Area]),
"IN",[Amount for Supplier costs],
"Corporate",[Amount for Supplier costs],
"PI",0,
"TR",CALCULATE([Amount for Supplier costs],ALLSELECTED('Case'),'Case'[Business Area] IN {"PI", "TR"})
)

[v-jingzhan-msft]

Hi @James_Ma 

You can create a new measure like below and use it in the table visual. 

Measure = 
SWITCH(SELECTEDVALUE('billing'[Business Area]),
    "IN",[Amount for Supplier costs],
    "Corporate",[Amount for Supplier costs],
    "PI",0,
    "TR",CALCULATE([Amount for Supplier costs],ALLSELECTED('billing'),'billing'[Business Area] IN {"PI", "TR"})
    )

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[James_Ma · Accepted solution]

Just looked at this again and added the Business area from the case table and  all seemed good, but PI has 0 values for internal and external, so ideally I need to add to the measure to only do this calc if the internal external column is internal.

Measure = SWITCH(SELECTEDVALUE('Case'[Business Area]),
"IN",[Amount for Supplier costs],
"Corporate",[Amount for Supplier costs],
"PI",0,
"TR",CALCULATE([Amount for Supplier costs],ALLSELECTED('Case'),'Case'[Business Area] IN {"PI", "TR"})
)

[v-jingzhan-msft · Accepted solution]

Hi @James_Ma So you can try

Measure = 
IF(SELECTEDVALUE('Table'[Internal/External])="Internal",
SWITCH(SELECTEDVALUE('Case'[Business Area]),
"IN",[Amount for Supplier costs],
"Corporate",[Amount for Supplier costs],
"PI",0,
"TR",CALCULATE([Amount for Supplier costs],ALLSELECTED('Case'),'Case'[Business Area] IN {"PI", "TR"})
),
[Amount for Supplier costs])

Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[James_Ma]

Hi Jing,

If I use this measure and there are no filters applied it still shows the PI amount, that is internal, in the PI total so does not count as 0 until I apply the "internal" filter in the visual?

Sorry if I am being a little dumb ob this one.

[James_Ma]

Thanks Jing, that works exactly how I need it to. Much appreicated help 

[James_Ma]

Hi Jing, thanks for that measure, just to say that hte Business Ares is from a different table called "Case" how would that effect the measure?

[James_Ma]

I needed to add that I wish to add teh Internal supplier costs from PI to the internal supplier costs for TR.