Selected value in slicer should affect only part of the measure

[L_K_]

Hello,

I really need some help with an idea I’m having.

Let's say I have a table like this:

Name_ID	Value_1	Measure
100	140.000
101	30.000
130	16.000
Total	186.000

I also made a table for calculating percentages in a way that, if I want to pick a percentage in a slicer, then the measure calculates the name_ID value (Value_1) with Value_2.

Name_displayed_in_slicer	Value_2
2%	1,02
5%	1,05
8%	1,08

I have a measure for the percentage values:

Sum_Value_2 = calculate(sum(table[Value_2]))

and then a measure that calculates (multiplies) the name_ID values (Value_1) with Value_2:

Measure  = Sum_Value_2 * Value_1

So now when I pick let's say 5% in the slicer, the values (Value_1) from name_ID will multiply by 1,05 (Value_2) so I get new values that are higher for 5% in the column for measure. The measure values are as seen in the next table:

Name_ID	Value	Measure
100	140.000	147.000
101	30.000	31.500
130	16.000	16.800
Total	186.000	195.300

Now to finally get to my problem.

I would like to add a slicer that when I pick a name_ID only that value multiplies by the selected value from the first slicer and others stay the same.

So, if I pick 5% in the first slicer and the name_ID 100 from the second slicer,

the result would be as seen in the next table:

Name_ID	Value	Measure
100	140.000	147.000
101	30.000	30.000
130	16.000	16.000
Total	186.000	193.000

How do I write a DAX formula that would make this possible? And then the total value to be correct as well?

A big thank you in advance!

[L_K_]

Hello @PaulOlding,

It took me some time to figure this out on my real data, but I eventually got it right and it works perfectly!

Thank you so much! 

[PaulOlding · Accepted solution]

Hi @L_K_ 

This solution uses a disconnected table called Names that just contains all the Name_ID values, and that is used in the Name_ID slicer.

I have a Percentages table that looks like this, so Multiplier is the equivalent of your Value_2:

The Measure is

Measure = 
VAR _Multiplier = SELECTEDVALUE(Percentages[Multiplier])
VAR _SelectedNameIDs = VALUES(Names[Name_ID])
VAR _WithMultiplier = 
SUMX(
    'Table',
    VAR _IsSelectedName = 'Table'[Name_ID] IN (_SelectedNameIDs)
    RETURN
        'Table'[Value] * _Multiplier * _IsSelectedName
)
VAR _WithoutMultiplier = 
SUMX(
    'Table',
    VAR _NotIsSelectedName = NOT('Table'[Name_ID] IN (_SelectedNameIDs))
    RETURN
        'Table'[Value] * _NotIsSelectedName
)
RETURN
    _WithMultiplier + _WithoutMultiplier

The _IsSelectedName and _NotIsSelectedName are true/false.  When you multiply by true/false DAX will treat true as 1 and false as 0.  So, if the Name_ID has been selected in the slicer it will return:

• Value * Multiplier for _WithMultiplier
• 0 for _WithoutMultiplier (as we multiply by false, and false is 0)

Final result