Forum Discussion

daniel_baciu's avatar
daniel_baciu
Helper I
3 years ago

Filtering a table using measures based on slicers

Hi,

 

This is a very easy question for the experienced users.

I have a table:

I want to show a visual (table/matrix) showing only the changes by project from a scenario to another. For example - result of changes in JUL scenario vs APR scenario should look like below, as there is only one change in project A between the 2 scenarios in the same period:

Currently, this is managed to be calculated using 2 manual measures where i filtered APR (see below) and JUL separately

 

APR = 
CALCULATE(
    SUM(Data[Value]),
    Filter(
        'data',
        'data'[Scenario] = "APR Scenario"
    )
)

 

 

and another measure ("Changes") where i deducted APR measure result from JUL measure:

 

 

Changes = [JUL] - [APR]

 

 

Question is - how can i automatically calculate the Changes by asking the user to select in a slicer the first scenario and the second scenario (like below)? 

 

User should select in the first one JUL and in the second one APR, and the result should appear in a table/matrix visual like below:

 

See here the pbix.

Thanks!

Need Help

3 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, daniel_baciu 

     

    You can try the following methods.

    Change = IF(HASONEVALUE(Data[Scenario]),SUM(Data[Value]),CALCULATE(MAX(Data[Value])-MIN(Data[Value]),ALLSELECTED(Data[Scenario])))

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • daniel_baciu's avatar
      daniel_baciu
      Helper I
      3 years ago

      Many Thanks!
      This works fine but, i didn't realize before one additional complexity. Your solutions works fine when there are the same number & name of projects in every scenario (project A, B, C appear in every scenario). 

       

      Unfortunately your solution doesn't work when projects disapear or new projects appear in future scenarios... I reattached your solution and have updated OCT Scenario (removed project C and added Project D). 

       

      Solution of the Change should be as follows:

      Project    Change

      A             100

      B              0

      C              -50

      D              60

       

      May i kindly ask you to have another look to see if you can provide an updated solution to this specific issue?

      Many thanks!

       

      File is here.