DAX formula to provide variation within the same values of a single column

[meozalp]

Hello,

This is my dataset:

Test Scenario	TestCategory 1	Test Category 2
Test1	5	10
Test2	6	9
Test3	7	8
Test4	3	4
Test5	10	12
Test6	5	6
Test7	9	10

I want to have a slicer and once either of the two test scenarios are selected, present the variation in results.

Test Scenario	TestCategory 1	Test Category 2
Test1	5	10
Test2	6	9
Variation	20%	-10%

How can I write a DAX measure that will calculate the Variation tab within the same column?

Thank you.

[JustJan · Accepted solution]

Hi @meozalp ,

A measure like this gets the result:

Value1 = 
var r = DISTINCTCOUNT('Table'[Test Scenario])
var v1 = SELECTEDVALUE('Table'[TestCategory 1])

var x1 = sumx(filter('Table', 'Table'[Test Scenario] = MIN('Table'[Test Scenario])),'Table'[TestCategory 1])
var x2 = sumx(filter('Table', 'Table'[Test Scenario] = Max('Table'[Test Scenario])),'Table'[TestCategory 1])

var result =
switch ( TRUE(), 
 r=1, v1,
 r=2, FORMAT(DIVIDE(x1-x2,x1), "0%"),
 "Too many tests selected")

return result

So the assumption here is that the test with the lower number is the 100%, for value2 just copy the measure and change the [Test Category1] in [Category 2]

Jan

if this is a solution for you, don't forget to mark it as such. thanks

[JustJan · Accepted solution]

Hello @meozalp 

The brackets in the formula are not in the right place.

var x1 = SUMX(FILTER('TestResultNtttcp','TestResultNtttcp'[TestScenario] = MIN('TestResultNtttcp'[TestScenario])),'TestResultNtttcp'[cpu])

var x2 = SUMX(FILTER('TestResultNtttcp','TestResultNtttcp'[TestScenario] = MAX('TestResultNtttcp'[TestScenario])),'TestResultNtttcp'[cpu])

Regards,

Jan

[AntrikshSharma · Accepted solution]

Download the PBI file from here: https://drive.google.com/file/d/1DSD_wjlDUu9Oibx8FSfwwreAHYrZMMd1/view?usp=sharing

I added an index column to do the calculations.

[meozalp]

@AntrikshSharma @JustJan Thanks a lot for your help.

[JustJan · Accepted solution]

Hello @meozalp 

The brackets in the formula are not in the right place.

var x1 = SUMX(FILTER('TestResultNtttcp','TestResultNtttcp'[TestScenario] = MIN('TestResultNtttcp'[TestScenario])),'TestResultNtttcp'[cpu])

var x2 = SUMX(FILTER('TestResultNtttcp','TestResultNtttcp'[TestScenario] = MAX('TestResultNtttcp'[TestScenario])),'TestResultNtttcp'[cpu])

Regards,

Jan

[meozalp]

Hi @JustJan ,

Thanks a lot for the prompt reply.

I am getting an error: Too few arguments were passed to the SUMX function. Could you please help reviewing it?

Value1 =

var r = DISTINCTCOUNT('TestResultNtttcp'[TestScenario])

var v1 = SELECTEDVALUE('TestResultNtttcp'[cpu])

var x1 = SUMX(FILTER('TestResultNtttcp','TestResultNtttcp'[TestScenario] = MIN('TestResultNtttcp'[TestScenario],'TestResultNtttcp'[cpu])))

var x2 = SUMX(FILTER('TestResultNtttcp','TestResultNtttcp'[TestScenario] = MAX('TestResultNtttcp'[TestScenario],'TestResultNtttcp'[cpu])))

var result =

SWITCH( TRUE(),

r=1, v1,

r=2, FORMAT(DIVIDE(x1-x2,x1), "0%"),

"Too many tests selected")

return result

Melih

[JustJan · Accepted solution]

Hi @meozalp ,

A measure like this gets the result:

Value1 = 
var r = DISTINCTCOUNT('Table'[Test Scenario])
var v1 = SELECTEDVALUE('Table'[TestCategory 1])

var x1 = sumx(filter('Table', 'Table'[Test Scenario] = MIN('Table'[Test Scenario])),'Table'[TestCategory 1])
var x2 = sumx(filter('Table', 'Table'[Test Scenario] = Max('Table'[Test Scenario])),'Table'[TestCategory 1])

var result =
switch ( TRUE(), 
 r=1, v1,
 r=2, FORMAT(DIVIDE(x1-x2,x1), "0%"),
 "Too many tests selected")

return result

So the assumption here is that the test with the lower number is the 100%, for value2 just copy the measure and change the [Test Category1] in [Category 2]

Jan

if this is a solution for you, don't forget to mark it as such. thanks