Filtering a table using measures based on slicers

[daniel_baciu]

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!

[v-zhangti]

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]

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.

[Idrissshatila]

Hello @daniel_baciu ,

Try using field parameters, check it out here https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin