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
Icon for Solution Sage rankSolution 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

    Is this possible without duplicating the fact table?? Obviously considering a much more complex scaled up example doubling the data is extremely performance prohibative. 

     

    Edited - Okay I misunderstood your solution and my apologies on that. After reviewing your file this does exactly what I'm looking for without duplicating the fact table. Nicely done that's really slick!!

  • 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