Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
Hello @meozalp
The brackets in the formula are not in the right place.
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.
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.
Hello @meozalp
The brackets in the formula are not in the right place.
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?
Melih
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
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |