One column in a matrix should not interact with Slicer

[rutujakadam_11]

I have a power bi report of finance domain which consist of matrix. In the rows field I have various Entity names of the company and for values I have created various DAX to calculate revenue generated by every Entity. The DAX I created are MTD, prior year MTD, SCENARIO sales MTD. There is also a Scenario Slicer which has various scenarios categoried under the sales generated such as Actual, FC2, FC3 and Budget. The MTD and prior year MTD DAX are created such that they will showcase the sales only for Actual scenario. It has that hard-coded value in the DAX. FOR Scenario sales MTD, initially the column showcases blank and only when field is selected from the slicer that scenario sales are showed. It uses Selected slicer option. Now the issue is that when I select for exple FC2 scenario from the slicer the ScenarioSales slicer column is populated but the MTD & Prior year MTD goes blank (as the hard-coded scenario is Actual for that DAX) How can I achieve a solution where the MTD & Prior Year MTD DAX are not affected by the fields selected in the slicer and remain constant and only Scenari Sales column is filtered based on slicer selection of Scenario?

MTD DAX I have used to calculate the MTD field in the matrix: 

MTD = TOTALMTD(

    SUM('vw_SXIRev_OST'[Converted_USD]),

    'vw_Date_Dimension'[DateKey - Copy],

    FILTER(

        vw_SXIRev_OST,

        (vw_SXIRev_OST[Acc_Type] = "Sales" || vw_SXIRev_OST[Acc_Type] = "Daily") &&

        vw_SXIRev_OST[Scenario] = "Actual"

    )

)

Pic1: Before field selected in scenario slicer

Pic 1

 
Pic2:After FC2 field selected in Scenario slicer.

Pic 2

[mauriciocardoso]

when i try to obtain data , via web, from the following site:

https://relatorioweb.com.br/horas-iguais/

i obtain the message: "not possible connect" - Access to the resource is forbidden.

how can i fix this ?

[rutujakadam_11]

I have prepared the DAX fo MTD & PriorYearMTD using (ALL) function for the requirement that they should not interact with Scenario Slicer. But now I want to create a third column which is the difference between MTD-PriorYearMTD. I am trying to do this calculation using the MTD & Prior Year MTD DAX but this column is now interacting with slicer. 

As I have used the ALL function to both DAXs, should'nt the result column perform similar output of not interacting with slicer?

[amitchandak · Accepted solution]

@rutujakadam_11 , Try if one of the two can help

MTD = TOTALMTD(
SUM('vw_SXIRev_OST'[Converted_USD]),
'vw_Date_Dimension'[DateKey - Copy],
FILTER(
(vw_SXIRev_OST),
(vw_SXIRev_OST[Acc_Type] = "Sales" || vw_SXIRev_OST[Acc_Type] = "Daily") &&
vw_SXIRev_OST[Scenario] = "Actual"
), all(vw_SXIRev_OST[Acc_Type]), all(vw_SXIRev_OST[Scenario])
)

MTD = calculate( TOTALMTD(
SUM('vw_SXIRev_OST'[Converted_USD]),
'vw_Date_Dimension'[DateKey - Copy]) ,
FILTER(
all(vw_SXIRev_OST[Acc_Type]),
(vw_SXIRev_OST[Acc_Type] = "Sales" || vw_SXIRev_OST[Acc_Type] = "Daily") ),
FILTER(
all(vw_SXIRev_OST[Scenario]),
vw_SXIRev_OST[Scenario] = "Actual"
)
)

[rutujakadam_11]

I have prepared the DAX fo MTD & PriorYearMTD using (ALL) function for the requirement that they should not interact with Scenario Slicer. But now I want to create a third column which is the difference between MTD-PriorYearMTD. I am trying to do this calculation using the MTD & Prior Year MTD DAX but this column is now interacting with slicer. 

As I have used the ALL function to both DAXs, should'nt the result column perform similar output of not interacting with slicer?

[rutujakadam_11]

Hi, the second DAX worked. Thanks.😊