Difference based on dynamic filter

[martynaaagesen]

Hi

I tried to search the forum for the answer, but I didnt quite find one that can help me. 

I have a table structured like below. BudgetID is my filter on the page, where I am selecting multiple different values. It is a column with multiple and not unique values. Selection I make is dynamic and I need to capture that. 

I also have a total in column CcTotalContractPaymentCBY. That Total I show as a sum. So per BudgetID - sum of CcTotal. 

Now, what I need is to show the difference between CcTotal, based on dynamic filter on the Page, also having a logic (CcTotal, filtered by BudgetID lower value - CcTotal, filtered by BudgetID higherValue)

You cannot imagine how greatful I will be, if you can help me out. 

[martynaaagesen]

Hi Again, Sorry it took me so much time.. I am not sure if the above works, MAYBE it does.. but the issue I have now, is that I am selecting those 2 budgetID's on the pages filter. This measure seems to disregard that fact and somehow when I throw it on the visual, it keeps it away.

any advice?

[tamerj1]

Hi @martynaaagesen 
If you are slicing by ID then it is simply the difference between the max and the min

=
VAR MaxCcTotal =
    MAX ( TableName[CcTotalContractPaymentCBY] )
VAR MinCcTotal =
    MIN ( TableName[CcTotalContractPaymentCBY] )
RETURN
    MinCcTotal - MaxCcTotal

[martynaaagesen]

thank you  for quick response! 

Unfortunately, I was not clear enough.

I am slicing by BudgetID. And I have 2 selected on the page. 

So I need to have a difference based on the BudgetID. BudgetID MAX and BudgetID MIN (as it is dynamic)

[tamerj1]

Hi @martynaaagesen 

A clean way to do that is by utilizing the column totals to disply the difference value. Activate the columns total from the visual format options and change the name from "Total" to "Difference". 

In the values of the matrix place this measure instead of the old one

=
VAR SelectedIDs =
    ALLSELECTED ( TableName[BudgetID] )
VAR MaxID =
    MAXX ( SelectedIDs, TableName[BudgetID] )
VAR MinID =
    MINX ( SelectedIDs, TableName[BudgetID] )
VAR MaxIDCcTotal =
    CALCULATE (
        SUM ( TableName[CcTotalContractPaymentCBY] ),
        TableName[BudgetID] = MaxID
    )
VAR MinIDCcTotal =
    CALCULATE (
        SUM ( TableName[CcTotalContractPaymentCBY] ),
        TableName[BudgetID] = MinID
    )
VAR CcTotal =
    SUM ( TableName[CcTotalContractPaymentCBY] )
RETURN
    IF ( HASONEVALUE ( TableName[BudgetID] ), CcTotal, MaxIDCcTotal - MinIDCcTotal )