Calculae measure based on slicer selection

[Anonymous]

Hi all

I have a table with two columns, one has names (18 different names), but are repeated multiple times,  and the other has values $

Name       Values

TOM	12.345
MARK	65.467
SARA	34.556
ELSA	34.356

What I want to do in my report is, when I select two names in a slicer the variance between the two names is calculated and posted on a card.

I tried to create a measure to find the fisrt and the last value but is not working:

AllValues: Sum[Values]

FirstValue =

CALCULATE (

    [AllValues];

    TOPN (

        1;

        FILTER(

            VALUES ( 'Base'[Name] );

            COUNTROWS ( VALUES ( 'Base'[Name] ) )

        )

    )

)

maybe there is a better way! open to options

thanks!

[parry2k]

@Anonymous what happens if not two name are selected (1 or more than 2 selected)?

[Anonymous]

Hi @parry2k  

well, I was trying to solve my problem by creating a table in a measure and using the measure to bring the first row  value of the table and then use a second measure to bring the second row value, therefore if more than two names are selected, the measures are going to ignore the order values.

my current measures are:

FirstValue =

CALCULATE (

    [AllValues];

    TOPN (

        1;

        FILTER(

            VALUES ( 'Base'[Name] );

            COUNTROWS ( VALUES ( 'Base'[Name] ))

        )

    )

)

SndValue = CALCULATE (

    [AllValues];

    TOPN (

        2;

        FILTER(

            VALUES ( 'Base'[Name] );

            COUNTROWS ( VALUES ( 'Base'[Name] ) )

        )

    )

)

- [FirstValue]

But using topN my measure is adding the values of the table, so I had to rest the first value to adjust.

and then: %Var:= SndValue/FirstValue-1

thanks for your comments, if you think that there is a better way of doing this, I am open to suggestions.

Fabian.

[parry2k · Accepted solution]

@Anonymous try this measure. 

Total = SUM( Table1[Values] )

Difference in Top 2 = 
VAR __Name1st = CALCULATE( [Total], TOPN( 1, VALUES(Table1[Name] ), [Total], DESC ) )
VAR __Name2ndTable = TOPN( 2, VALUES(Table1[Name] ), [Total], DESC ) 
VAR __Name2nd = MINX( __Name2ndTable, [Total] )
RETURN __Name1st - __Name2nd

[alan7lp]

Hello @parry2k 

This measure has helped me with a similar problem however I do have a question about it, please:

When the variance is negative, it's showing it as positve still but I need it to show if it's negative.

My original data:

RANGE	NEED DIFF PREV WK (PCS)	NEED DIFF PREV WK (%)
Product 1	-3,848	-3.63%
Product 2	488	0.06%
Product 3	935	0.85%
Product 4	1,008	2.71%
Product 5	1,508	1.25%
Product 6	2,054	1.36%
Product 7	4,461	0.80%

Data with your solution:

RANGE	Difference in Top 2	Difference in Top 2 %
Product 1	3,848	3.77%
Product 2	488	0.06%
Product 3	935	0.85%
Product 4	1,008	2.71%
Product 5	1,508	1.25%
Product 6	2,054	1.36%
Product 7	4,461	0.80%

As you can see, it's not showing the variance as negative value (like it should be) therefore the % calculation is not correct. 

Would you have a solution for this?

Thanks!

[Anonymous]

@parry2kBrilliant!
Thanks a lot!