Forum Discussion

martynaaagesen's avatar
martynaaagesen
Regular Visitor
4 years ago

Difference based on dynamic filter

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 multipl...
Need Help
tamerj1's avatar
tamerj1
Community Champion
4 years ago

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's avatar
    martynaaagesen
    Regular Visitor
    4 years ago

    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's avatar
      tamerj1
      Community Champion
      4 years ago

      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 )