Forum Discussion

VinayakKarne's avatar
VinayakKarne
Frequent Visitor
6 years ago
Solved

When multiple values are selected in Filter, Add/Subtract values in the DataCards

Hi Team,

Need your opinion on below issue:

 

I have a filter "Transactions" which has two values:

1.Giver

2.Receiver

 

By default, when i select "Giver", i get sum value in the DataCard.

Same for the "Receiver".

 

Now what i want is when i select both "Giver" and " Receiver",  data card should provide me the value which is  ["Giver Record Values" - "Receiver Record Values"].

Also when there is no value selected, i want to show the sum of all the records.

 

I tried using "Calculated Column" where i marked "Receiver" values (-).  But when nothing is selected i get subtracted values.

How do i achieve this.

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi, VinayakKarne 

    For your case, just try this logic to create a measure

    Measure = 
IF (
    ISFILTERED ( 'Table'[Type] ),
    IF (
        HASONEVALUE ( 'Table'[Type] ),
        CALCULATE ( SUM ( 'Table'[Value] ) ),
        CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Giver" )
            - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Receiver" )
    ),
    CALCULATE ( SUM ( 'Table'[Value] ) )
)

    Result:

    Simple sample data

     

    select "Giver"select "Receiver"no value selectedselect both "Giver" and " Receiver"

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi, VinayakKarne 

    For your case, just try this logic to create a measure

    Measure = 
IF (
    ISFILTERED ( 'Table'[Type] ),
    IF (
        HASONEVALUE ( 'Table'[Type] ),
        CALCULATE ( SUM ( 'Table'[Value] ) ),
        CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Giver" )
            - CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Type] = "Receiver" )
    ),
    CALCULATE ( SUM ( 'Table'[Value] ) )
)

    Result:

    Simple sample data

     

    select "Giver"select "Receiver"no value selectedselect both "Giver" and " Receiver"

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    6 years ago

    VinayakKarne  Please provide some sample test data and expected output which will be helpful to provide an accurate solution.

    But by the look of it, you need to create a Measure not a calculated column. In the measure, use the selectedvalue to capture the filter selection.

     

    Sample test data will be really helpful !