Forum Discussion

Naveen_SV's avatar
Naveen_SV
Icon for Helper IV rankHelper IV
5 years ago
Solved

Looking for difference between two values by filters

Hi All,

 

I am looking for difference between two values like q2 and a3 cycles result should display the difference values based on the filter selected.

below the example: when i select the q2 and q3 it should display difference between those values at the end.

 

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Naveen_SV ,

    I created a sample pbix file (see attachment) for you, please check whether that is what you want.

    1. Create a calculated table: Table 2

    Table 2 = UNION(VALUES('Table'[Cycle]),ROW("Cycle","XDifference"))

    2. Create measures to get the sum amount and difference

    Measure = 
 VAR _sAmountofQ1=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ID] = MAX ( 'Table'[ID] ) &&'Table'[Cycle]="Q1"))
 VAR _sAmountofQ2=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ID] = MAX ( 'Table'[ID] ) &&'Table'[Cycle]="Q2"))
 VAR _sAmountofQ3=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ID] = MAX ( 'Table'[ID] ) &&'Table'[Cycle]="Q3"))
 VAR _sAmountofQ4=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ID] = MAX ( 'Table'[ID] ) &&'Table'[Cycle]="Q4"))
return  SWITCH (
        SELECTEDVALUE ( 'Table 2'[Cycle] ),
        "XDifference",_sAmountofQ4-_sAmountofQ3-_sAmountofQ2-_sAmountofQ1,
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER ( 'Table', 'Table'[ID] = MAX ( 'Table'[ID] )&&'Table'[Cycle]=MAX('Table 2'[Cycle]) )
        )
    ) 
    Difference = SUMX(VALUES('Table'[ID]),[Measure])

    3. Create a matrix visual (Rows: ID   Column: field Cycle of Table 2  Values: [Difference] )

    Best Regards
    Rena
    Community Support Team _ Rena Ruan
    If this post helps, then please consider Accept it as the solution to help the other members find it more.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Naveen_SV ,

    What's your expected result? Whether the below table is what you want? If yes, you can refer the following threads to get it.

    ID Q1 Q2 Q3 difference
    23 　 245 　 245
    24 45 　 27.89 -17.11
    25 99.9 　 　 99.9

    Subtract values in a measure for different categories

    Dax - how to subtract one pivoted matrix column from another (power bi)

    Best Regards

    Rena

    Community Support Team _ Rena Ruan
    If this post helps, then please consider Accept it as the solution to help the other members find it more.

    • Naveen_SV's avatar
      Naveen_SV
      Icon for Helper IV rankHelper IV
      5 years ago

      Anonymous  Thanks for your reply, where here the scenrio is q1, q2, q3 is dynamic 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Naveen_SV ,

        I created a sample pbix file (see attachment) for you, please check whether that is what you want.

        1. Create a calculated table: Table 2

        Table 2 = UNION(VALUES('Table'[Cycle]),ROW("Cycle","XDifference"))

        2. Create measures to get the sum amount and difference

        Measure = 
 VAR _sAmountofQ1=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ID] = MAX ( 'Table'[ID] ) &&'Table'[Cycle]="Q1"))
 VAR _sAmountofQ2=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ID] = MAX ( 'Table'[ID] ) &&'Table'[Cycle]="Q2"))
 VAR _sAmountofQ3=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ID] = MAX ( 'Table'[ID] ) &&'Table'[Cycle]="Q3"))
 VAR _sAmountofQ4=CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[ID] = MAX ( 'Table'[ID] ) &&'Table'[Cycle]="Q4"))
return  SWITCH (
        SELECTEDVALUE ( 'Table 2'[Cycle] ),
        "XDifference",_sAmountofQ4-_sAmountofQ3-_sAmountofQ2-_sAmountofQ1,
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER ( 'Table', 'Table'[ID] = MAX ( 'Table'[ID] )&&'Table'[Cycle]=MAX('Table 2'[Cycle]) )
        )
    ) 
        Difference = SUMX(VALUES('Table'[ID]),[Measure])

        3. Create a matrix visual (Rows: ID   Column: field Cycle of Table 2  Values: [Difference] )

        Best Regards
        Rena
        Community Support Team _ Rena Ruan
        If this post helps, then please consider Accept it as the solution to help the other members find it more.