Card value depending which column is filtered

Question

Hi everyone,I have 2 different columns which are filters: Month and Quarter.&nbsp;I also have a card which I need calculation depending which filter is applied. For example:&nbsp;I have 2 numeric columns (Amount1 and Amount2)If I do a month filter, so take the value Amount1 and substract Amount2 of the month.If I do a quarter filter, I take the value of SUM(Amount1) and need substract the last value of the Quarter for Amount 2.&nbsp;Month&nbsp; &nbsp; &nbsp;-&nbsp; &nbsp; &nbsp;Quarter&nbsp; &nbsp; &nbsp;-&nbsp; &nbsp; &nbsp;Amount1&nbsp; &nbsp; &nbsp;-&nbsp; &nbsp; &nbsp;Amount201&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10002&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 153&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;80003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 543&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;60005&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30006&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;200If I filter Month 05, so 234 - 300If I filter Q2, so 543+234+123 - 200&nbsp;If I filter Month 01, so 100 - 100If I filter Q1, so 100+153+123 - 400&nbsp;I guess is a IF but depending which table/column is filtered and active, but I cannot find nothing.&nbsp;Thanks!!

jihwan_kim · Accepted Answer

Hi,
Please check the below picture and the attached pbix file.
&nbsp;

&nbsp;
Expected result measure: =
SWITCH (
    TRUE (),
    HASONEFILTER ( Data[Month] ), SUM ( Data[AmountOne] ) - SUM ( Data[AmountTwo] ),
    HASONEFILTER ( Data[Quarter] ),
        SUM ( Data[AmountOne] )
            - CALCULATE (
                SUM ( Data[AmountTwo] ),
                FILTER ( VALUES ( Data[Month] ), Data[Month] = MAX ( Data[Month] ) )
            )
)

&nbsp;

Forum Discussion

Card value depending which column is filtered

1 Reply

Related content

Dependent Filters and Filter Depend on Slicer

Filter dependencies

Filter depending on another filter

Circular dependency

Slicer for filtering several columns