Forum Discussion

ngocnguyen's avatar
ngocnguyen
Helper IV
5 years ago
Solved

Compare data with conditional slicer

Hi,

I have 3 tables as belows. I wanna create slicer of customer, in which. when I choose 1 specific customer (Ex: Customer A) => It will show the the visual as "result1". 

If I don't choose that slicer, it will show the visual as " result2"

 

So, what Dax formulas can I use ?

Thanks in advance for your support!

 

I

Need Help
  • timg's avatar
    timg
    5 years ago

    Hi Ngocnguyen,

     

    This might be worth a try:

     

    Measures:

    ActualSum = SUM(CustomerActual[ActualAmount])
    TargetCalculation = IF(ISFILTERED(CustomerActual),SUM(CustomerTarget[CustomerTarget]),SUM(TotalTarget[Target]))
    Compare = CustomerActual[ActualSum] - TotalTarget[TargetCalculation]
     
    The ISFILTERED funciton can be used to differentiate between the unfiltered table and when a specific customer is selected. When paired with a slicer, the TargetCalculation measure would result in the sum of the selected customer, or the sum of the TotalTarget table, based on the slicer selection.
     
    Customer selected:
     
    No customer selected:
     
    Hope this helps!
     
    Best regards,
     
    Tim
  • lkalawski's avatar
    lkalawski
    5 years ago

    Hi ngocnguyen 

    I prepared a solution for you. 

    You can create this 4 measures:

    Selected Customer = 
IF(ISFILTERED(Table1[Customer]), CONCATENATEX(DISTINCT(Table1[Customer]), Table1[Customer],","),
"ALL Customers")

Actual = 
Sum(Table1[Actual Amount])

Target = 
IF(ISFILTERED(Table1[Customer]),
    Sum(Table2[Target]),
    MAX(Table3[Target])
)

Compare = [Actual] - [Target]

     

    Proud to be a Super User.
    If I helped, please accept the solution and give kudos! 
    Linkedin

     

2 Replies

  • timg's avatar
    timg
    Solution Sage
    5 years ago

    Hi Ngocnguyen,

     

    This might be worth a try:

     

    Measures:

    ActualSum = SUM(CustomerActual[ActualAmount])
    TargetCalculation = IF(ISFILTERED(CustomerActual),SUM(CustomerTarget[CustomerTarget]),SUM(TotalTarget[Target]))
    Compare = CustomerActual[ActualSum] - TotalTarget[TargetCalculation]
     
    The ISFILTERED funciton can be used to differentiate between the unfiltered table and when a specific customer is selected. When paired with a slicer, the TargetCalculation measure would result in the sum of the selected customer, or the sum of the TotalTarget table, based on the slicer selection.
     
    Customer selected:
     
    No customer selected:
     
    Hope this helps!
     
    Best regards,
     
    Tim
  • lkalawski's avatar
    lkalawski
    Resident Rockstar
    5 years ago

    Hi ngocnguyen 

    I prepared a solution for you. 

    You can create this 4 measures:

    Selected Customer = 
IF(ISFILTERED(Table1[Customer]), CONCATENATEX(DISTINCT(Table1[Customer]), Table1[Customer],","),
"ALL Customers")

Actual = 
Sum(Table1[Actual Amount])

Target = 
IF(ISFILTERED(Table1[Customer]),
    Sum(Table2[Target]),
    MAX(Table3[Target])
)

Compare = [Actual] - [Target]

     

    Proud to be a Super User.
    If I helped, please accept the solution and give kudos! 
    Linkedin