Compare data with conditional slicer

[ngocnguyen]

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

[timg · Accepted solution]

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 · Accepted solution]

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

[timg · Accepted solution]

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