Dynamic Measure based on filter

[ignas]

Hello

I have been struggling to get it seems pretty easy thing.

I have Actual, Budget and Forecast data for the same period. Actual has zeros from July.

I want to have such graph:

• If we select BUD in the filter:  then it should show ACT data where ACT is not equal to 0, and where it is equal to 0 has to display BUD data
• If we select FC1 in the filter:  then it should show ACT data where ACT is not equal to 0, and where it is equal to 0 has to display FC1 data

 

Please help me with it. I cannot get it working.

Here is the file: https://mega.nz

Regards,

Ignas

[alexei7]

Hi @ignas,

I think I've got a solution that works for you.

See if the following does what you intend:

https://mega.nz/#!PeJyjS4D!USpHv_LaB6q9RpEq9cKNWMXFDCZ8VkrZHwahfaYwEPI

Hope that helps,

Alex

[ignas]

Hello @alexei7

Thanks a lot. Unfortunately it is not what I need

I should have only BUD and FC1 in filter.

ACT data should always be present in the graph. If we selected BUD then for months that ACT does not have data should be seen BUD data.

If we select  FC1 in filter then we should see FC1 for month where ACT has no data.

Here is how it should look like:

[ignas]

Hey @alexei7

Here is my desired result just it should be in one graph and it should switch depending on the filter between BUD and FC1:

Here is a new file:

https://mega.nz/#!LrpRnSwR!DU_Bir3jYPSU139nSPgsbRuTKsJCeqozj6eKDrAhzbo

[OwenAuger · Accepted solution]

Hi @ignas

Here is one way of doing it.

PBIX link

Set up the data model like this:

The Scenario table is just a single-column table with ACT, BUD, FC1. This table can be hidden

The Scenario Combined table looks like this. The Scenario column could be hidden. It could be created automatically in Power Query:

Scenario Combined	Scenario
BUD	ACT
BUD	BUD
FC1	ACT
FC1	FC1

Then I defined measures as follows:

Amount Sum = 
SUM ( Data[Amount] )

Amount Final = 
SUMX (
    VALUES ( Data[Date] ),
    VAR ActualAmount =
        CALCULATE ( [Amount Sum], Scenario[Scenario] = "ACT" )
    RETURN
        IF (
            ActualAmount = 0,
            [Amount Sum],
            CALCULATE ( [Amount Sum], KEEPFILTERS ( Scenario[Scenario] = "ACT" ) )
        )
)
            

Then with a slicer on 'Scenario Combined'[Scenario], your visual behaves as expected.

Well, that's one suggestions. Have a play with that and see if it works for you 🙂

Regards,

Owen