topic Re: Calculae measure based on slicer selection in DAX Commands and Tips

Calculae measure based on slicer selection

Anonymous — Tue, 04 Jun 2019 14:54:52 GMT

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 (

FILTER(

VALUES ( 'Base'[Name] );

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

)

maybe there is a better way! open to options

thanks!

Re: Calculae measure based on slicer selection

parry2k — Tue, 04 Jun 2019 23:36:26 GMT

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

Re: Calculae measure based on slicer selection

Anonymous — Wed, 05 Jun 2019 14:34:52 GMT

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 (

FILTER(

VALUES ( 'Base'[Name] );

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

)

SndValue = CALCULATE (

[AllValues];

TOPN (

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.

Re: Calculae measure based on slicer selection

parry2k — Wed, 05 Jun 2019 14:47:17 GMT

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

Re: Calculae measure based on slicer selection

Anonymous — Wed, 05 Jun 2019 15:11:00 GMT

parry2kBrilliant!
Thanks a lot!

Re: Calculae measure based on slicer selection

Anonymous — Fri, 17 Jan 2020 13:32:54 GMT

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!