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...
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