Create a measure that can calculate values based a slicer selection from a master table.

[ck1995]

HI,

I have 2 tables,

Region master : 

Branch	Region
Delhi 1	NR 1
Ludhiana	NR 1
Chandigarh	NR 1
Dehradun	NR 1
Delhi 3	NR 1
Sonipat	NR 1
Delhi 2	NR 1
Indore	NR 2
Raipur	NR 2
Noida	NR 2
Gurgaon	NR 2
Agra	NR 2
Lucknow	NR 2
Kolkata	NR 2
Ranchi	NR 2

Budget

Region	Branch / Area	Budget
NR 1	Chandigarh	965
NR 1	Dehradun	686
NR 1	Delhi 1	620
NR 1	Delhi 2	289
NR 1	Delhi 3	968
NR 1	Ludhiana	820
NR 1	Sonipat	713
NR 1	NR 1	184
NR 2	Agra	280
NR 2	Gurgaon	219
NR 2	Faridabad	700
NR 2	Indore	697
NR 2	Kolkata	807
NR 2	Lucknow	512
NR 2	Noida	527
NR 2	Raipur	744
NR 2	ROE 1	488
NR 2	ROE 2	979
NR 2	NR 2	729
India	India	162

I have two slicers based on region master branch & region.

I want 3 conditions that should work based on slicer selection and values that are fetched,

Condition 1: if no selection is made in the slicer : Value to be fetched and displayed Should be of "India" from Budget table,

COndition 2: If Region is selected in slicer  : Value to be fetched and displayed should be of "Selected REgion" from Budget table

Condition 3: If Branch is selected in slicer : Value to be fetched and displayed should be of "selected branch" from budget table.

Budget = var Region = SELECTEDVALUE('Region Master'[Region],"India")
var Branch = SELECTEDVALUE('Region Master'[Branch])
var calc = if(iscrossfiltered('BU 2022 (2)'[Branch / Area]),
            CALCULATE(SUM('BU 2022 (2)'[Bids Elev]),'BU 2022 (2)'[Branch / Area]=Region || 'BU 2022 (2)'[Branch / Area]=Branch),
            sum('BU 2022 (2)'[Bids Elev]))
var decimals = "0.00"
RETURN
    SWITCH ( TRUE(),
        calc > 1000000 , FORMAT ( calc / 1000 , decimals & "M" ),
        calc > 1000 , FORMAT ( calc / 1000 , decimals & "K" ),
        calc < 1000 , FORMAT(calc , decimals ),
        FORMAT ( calc , decimals )
    )

*The reason to add a region column in budget table is if in a region two branches are selected the sum should be displayed.

Kindly support.

[ck1995]

@amitchandak I have a new development, as a Date table has been added a new slicer is created on the canvas. But budget does not have any relationship with date as budget is yearly so when the date slicer is selected, the values go blank. I tried using SUMX but the whole logic we created doesn't work. Also if i use the date slicer can we calculate (Budget / 12 )* (selected slicer month no).

[amitchandak · Accepted solution]

@ck1995 , try like

Budget = var Region = summarize(allselected('Region Master'), 'Region Master'[Region])
var Branch = summarize(allselected('Region Master'), 'Region Master'[Branch])
var calc = if(isfilter('Region Master'[Region]) || isfilter('Region Master'[Region]) ,
CALCULATE(SUM('BU 2022 (2)'[Bids Elev]),'BU 2022 (2)'[Region] in Region && 'BU 2022 (2)'[Branch / Area] in Branch),
CALCULATE(SUM('BU 2022 (2)'[Bids Elev]),'BU 2022 (2)'[Region] = "India"))
var decimals = "0.00"
RETURN
SWITCH ( TRUE(),
calc > 1000000 , FORMAT ( calc / 1000 , decimals & "M" ),
calc > 1000 , FORMAT ( calc / 1000 , decimals & "K" ),
calc < 1000 , FORMAT(calc , decimals ),
FORMAT ( calc , decimals )
)

[ck1995]

Thank you so much, Appreciate your help. It worked perfectly.