Forum Discussion

sparker95's avatar
sparker95
Frequent Visitor
3 years ago
Solved

Filtering using slicer

 I have a table with columns for 'Status' which has 5 options {1. Fee Forecast, 2. Confirmed, 3. Likely, 4. Planning, 5. Spec}, another that is 'Date' that gives me {Q1, Q2, Q3, Q4} and finally a 'Fee' column that has £ values in it. 

On my page I have then built 5 slicers that take values from -100 to 100, I want to use these 5 slicers to adjust each of the 5 statuses independently. 

 

I created the code to adjust Fee using the '1.Fee Forecast' slicer, however I need it to take into account each of the slicers independently.

 

Status & Quarter =
CALCULATE(
    SUM(Table2[Fee]), VALUES(Table2[Date]),
    VALUES(Table2[status])
) * (1 + '1. Fee Forecast %.'[Fee Forecast %. Value] / 100)
 
I presume using something like SWITCH function to take value from status and then alter according to the 
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    3 years ago

    Hello sparker95,

     

    Can you please try:

     

    1. Create Individual Slicer Measures

    Fee Forecast % = [1. Fee Forecast %. Value]
Confirmed % = [2. Confirmed %. Value]
Likely % = [3. Likely %. Value]
Planning % = [4. Planning %. Value]
Spec % = [5. Spec %. Value]

    2. Create Individual Adjustment Measures

    Fee Forecast Adjusted =
SUM(Table2[Fee]) *
(1 + [Fee Forecast %] / 100)

Confirmed Adjusted =
SUMX(
    FILTER(Table2, Table2[Status] = "Confirmed"),
    Table2[Fee] * (1 + [Confirmed %] / 100)
)

Likely Adjusted =
SUMX(
    FILTER(Table2, Table2[Status] = "Likely"),
    Table2[Fee] * (1 + [Likely %] / 100)
)

Planning Adjusted =
SUMX(
    FILTER(Table2, Table2[Status] = "Planning"),
    Table2[Fee] * (1 + [Planning %] / 100)
)

Spec Adjusted =
SUMX(
    FILTER(Table2, Table2[Status] = "Spec"),
    Table2[Fee] * (1 + [Spec %] / 100)
)

    3. Create a Master Measure to Combine Adjusted Values

    Adjusted Fee Total =
[Fee Forecast Adjusted] + [Confirmed Adjusted] + [Likely Adjusted] +
[Planning Adjusted] + [Spec Adjusted]

    Should you require further assistance, please do not hesitate to reach out to me.

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    Hello sparker95,

     

    Can you please try:

     

    1. Create Individual Slicer Measures

    Fee Forecast % = [1. Fee Forecast %. Value]
Confirmed % = [2. Confirmed %. Value]
Likely % = [3. Likely %. Value]
Planning % = [4. Planning %. Value]
Spec % = [5. Spec %. Value]

    2. Create Individual Adjustment Measures

    Fee Forecast Adjusted =
SUM(Table2[Fee]) *
(1 + [Fee Forecast %] / 100)

Confirmed Adjusted =
SUMX(
    FILTER(Table2, Table2[Status] = "Confirmed"),
    Table2[Fee] * (1 + [Confirmed %] / 100)
)

Likely Adjusted =
SUMX(
    FILTER(Table2, Table2[Status] = "Likely"),
    Table2[Fee] * (1 + [Likely %] / 100)
)

Planning Adjusted =
SUMX(
    FILTER(Table2, Table2[Status] = "Planning"),
    Table2[Fee] * (1 + [Planning %] / 100)
)

Spec Adjusted =
SUMX(
    FILTER(Table2, Table2[Status] = "Spec"),
    Table2[Fee] * (1 + [Spec %] / 100)
)

    3. Create a Master Measure to Combine Adjusted Values

    Adjusted Fee Total =
[Fee Forecast Adjusted] + [Confirmed Adjusted] + [Likely Adjusted] +
[Planning Adjusted] + [Spec Adjusted]

    Should you require further assistance, please do not hesitate to reach out to me.