Forum Discussion

ignas's avatar
ignas
Advocate II
8 years ago
Solved

Dynamic Measure based on filter

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

Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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 CombinedScenario
    BUDACT
    BUDBUD
    FC1ACT
    FC1FC1

     

    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

4 Replies