Forum Discussion

BIQuest321's avatar
BIQuest321
Frequent Visitor
3 years ago
Solved

Subtract values based on two slicer selections

Hey All,   Been noodling this all day and coming up short so far. Essentially I will have two slicers that are supposed to offer dynamic comparisons of fact table data. These are basic calculations...
Tips and Tricks
Tutorial Requests
  • bolfri's avatar
    bolfri
    3 years ago

     

     

    Create 2 tables:

    Option A = GROUPBY(SampleData,SampleData[Name])
    Option B = GROUPBY(SampleData,SampleData[Name])
     
    Create 3 measures:
    Option A Value = CALCULATE([Average ID],SampleData[Name] in VALUES('Option A'[Name]))
    Option B Value = CALCULATE([Average ID],SampleData[Name] in VALUES('Option B'[Name]))
    B minus A = [Option B Value] - [Option A Value]
     
    You can add also 2 more bonus measures to prevent from selecting same name in A & B at the same time:
    Not in A = IF(SELECTEDVALUE('Option B'[Name]) in VALUES('Option A'[Name]),0,1)
    Not in B = IF(SELECTEDVALUE('Option A'[Name]) in VALUES('Option B'[Name]),0,1)
    Put those measures on filters with condition = 1.
     

     

    In this case you can't select Aetna, because it's on Option A, but if on option A will be Bolfri > Bolfri will dissapear from Option B, and Aetna will be avaliable to select.

     

    PBIX file: https://we.tl/t-GVWssWxBpX

     

bolfri's avatar
bolfri
Solution Sage
3 years ago

 

 

Create 2 tables:

Option A = GROUPBY(SampleData,SampleData[Name])
Option B = GROUPBY(SampleData,SampleData[Name])
 
Create 3 measures:
Option A Value = CALCULATE([Average ID],SampleData[Name] in VALUES('Option A'[Name]))
Option B Value = CALCULATE([Average ID],SampleData[Name] in VALUES('Option B'[Name]))
B minus A = [Option B Value] - [Option A Value]
 
You can add also 2 more bonus measures to prevent from selecting same name in A & B at the same time:
Not in A = IF(SELECTEDVALUE('Option B'[Name]) in VALUES('Option A'[Name]),0,1)
Not in B = IF(SELECTEDVALUE('Option A'[Name]) in VALUES('Option B'[Name]),0,1)
Put those measures on filters with condition = 1.
 

 

In this case you can't select Aetna, because it's on Option A, but if on option A will be Bolfri > Bolfri will dissapear from Option B, and Aetna will be avaliable to select.

 

PBIX file: https://we.tl/t-GVWssWxBpX

 

BIQuest321's avatar
BIQuest321
Frequent Visitor
3 years ago

Hey bolfri,

 

New day new problem. With the original premise basically still in tact here, how could I display the actual descriptions that are different or in common between A and B?

For example:
Option A has: A, B, C, D, E
Option B has: B, E, X, Y, Z

Result would return what they do or don't have in common:

Result (A,B): X, Y, Z
Result (B,A): A, C, D