Budget Allocation without relationship

[admin_xlsior]

Hi,

I'm wondering can we calculate Budget, without relationship ?

For example I have this table

Dealer	Region	Branch	Year	Month	WO group	Budget
DEMF	Washington Reg	Washington	Calendar 2019	January 2019	External	160.00
DEMF	Washington Reg	Washington	Calendar 2019	February 2019	External	220.00
DEMF	Washington Reg	Washington	Calendar 2019	March 2019	External	180.00
DEMF	Washington Reg	Washington	Calendar 2019	April 2019	External	100.00
DEMF	Washington Reg	Washington	Calendar 2019	January 2019	Internal	160.00
DEMF	Washington Reg	Washington	Calendar 2019	February 2019	Internal	220.00
DEMF	Washington Reg	Washington	Calendar 2019	March 2019	Internal	180.00
DEMF	Washington Reg	Washington	Calendar 2019	April 2019	Internal	200.00
DEMF	Washington Reg	Washington	Calendar 2019	January 2019	Warranty	160.00
DEMF	Washington Reg	Washington	Calendar 2019	February 2019	Warranty	220.00
DEMF	Washington Reg	Washington	Calendar 2019	March 2019	Warranty	220.00
DEMF	Washington Reg	Washington	Calendar 2019	April 2019	Warranty	260.00
DEMF	California Reg	California	Calendar 2019	January 2019	External	160.00
DEMF	California Reg	California	Calendar 2019	February 2019	External	220.00
DEMF	California Reg	California	Calendar 2019	March 2019	External	180.00
DEMF	California Reg	California	Calendar 2019	April 2019	External	160.00
DEMF	California Reg	California	Calendar 2019	January 2019	Internal	120.00
DEMF	California Reg	California	Calendar 2019	February 2019	Internal	200.00
DEMF	California Reg	California	Calendar 2019	March 2019	Internal	100.00
DEMF	California Reg	California	Calendar 2019	April 2019	Internal	80.00
DEMF	California Reg	California	Calendar 2019	January 2019	Warranty	160.00
DEMF	California Reg	California	Calendar 2019	February 2019	Warranty	220.00
DEMF	California Reg	California	Calendar 2019	March 2019	Warranty	280.00
DEMF	California Reg	California	Calendar 2019	April 2019	Warranty	160.00

Then I created this measures :

Budget Allocation =

    VAR CurrentMonth = SELECTEDVALUE('Date'[Month])

    VAR MonthlyBudgetAmt = CALCULATE([Total Budget-GP], FILTER( ALL('Budget' [Month]), CurrentMonth = 'Budget'[Month]

    )

    )

return MonthlyBudgetAmt

This is return correct value :

Month	Budget
January 2019	920
February 2019	1300
March 2019	1140
April 2019	960

However I wan to add the other filter like Region and Branch. The slicer, and also the table visualization might need to display those value as well.

So at first I tried to add the region frst like this :

Budget Allocation =

    VAR CurrentMonth = SELECTEDVALUE('Date'[Month])

    VAR CurrentRegion = SELECTEDVALUE('Region'[Region])

    VAR MonthlyBudgetAmt = CALCULATE([Total Budget-GP], FILTER( ALL('Budget' [Month]), CurrentMonth = 'Budget'[Month],

FILTER( ALL('Budget' [Region]), CurrentRegion = 'Budget'[Region]

    )

    )

return MonthlyBudgetAmt

It just give me blank value.

Thanks,

[v-cherch-msft · Accepted solution]

Hi @admin_xlsior 

You may try below measure or just use budget table's region column as slicer.For further,please refer to attached sample file.

Measure =
VAR CurrentMonth =
    SELECTEDVALUE ( 'Date'[Month] )
VAR CurrentRegion =
    SELECTEDVALUE ( 'Region'[Region] )
VAR MonthlyBudgetAmt =
    CALCULATE (
        [Total Budget-GP],
        FILTER (
            ALL ( 'Budget' ),
            CurrentMonth = 'Budget'[Month]
                && CurrentRegion = 'Budget'[Region]
        )
    )
RETURN
    MonthlyBudgetAmt

Regards,