Calculate for sub region ( allocate sales budget)

[luisClive]

Hi All

Can you help with following?

I have the following  main table

Sales
Month	OU	Sales
Jan	Asia	204
Jan	Europe	234
Jan	Pacific	211
Feb	Asia	456
Feb	Europe	233
Feb	Pacific	123
March	Asia	345
March	Europe	321
March	Pacific	179

and a sub table ( wherein asia in broken into 3 parts by %)

Sub Region	(Main region is Asia)
South East Asia	40%
ME	35%
North	25%

I want a sales measure for sub region ( or calculated column if its a better approach)

the solution shold be like

Sales
Month	OU	Solution
Jan	South East Asia	40% of  204
Jan	ME	35% of  204
Jan	North	25% of  204
Feb	South East Asia
Feb	ME
Feb	North
March	South East Asia
March	ME
March	North

of course these are samples, actual table are quite big

[v-alq-msft]

Hi, @luisClive 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Sales:

Sub:

You may create a measure as below.

Result = 
var s = 
CALCULATE(
        SUM(Sales[Sales]),
        FILTER(
            ALL(Sales),
            [Month]=MAX(Sales[Month])&&
            [OU]="Asia"
        )
)
var v = LOOKUPVALUE(Sub[(Main region is Asia)],Sub[Sub Region],MAX(Sub[Sub Region]))

return
s*v

Result:

Best Regards

Allan

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

[luisClive]

@v-alq-msft 

many many thanks

How do i adjust the formula to return grand total instead of just max?

[v-alq-msft · Accepted solution]

Hi, @luisClive 

You may modify the measure as below. The pbix file is attached in the end.

Result = 
var tab = 
ADDCOLUMNS(
    CROSSJOIN(
        DISTINCT(Sales[Month]),
        DISTINCT(Sub[Sub Region])
    ),
    "Result",
    var month = [Month]
    var subregion = [Sub Region]
    var s = 
    CALCULATE(
            SUM(Sales[Sales]),
            FILTER(
                ALL(Sales),
                [Month]=month&&
                [OU]="Asia"
            )
    )
    var v = LOOKUPVALUE(Sub[(Main region is Asia)],Sub[Sub Region],subregion)
    return
    s*v
)

return
SUMX(
    tab,
    [Result]
)

Result:

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[luisClive]

Many thanks for help

[amitchandak]

@luisClive , Create a new table like.

filter(crossjoin(sales,sub),sales[OU] =sub[region])

crossjoin need all columns to different across table, so if they are same you can rename usinf select columns

refer

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[luisClive]

@amitchandak 

did not work for me

this part is not clear

sales[OU] =sub[region])

Not working because of no unique values on both tables?

all columns are different for me