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

ignas's avatar
ignas
Advocate II
8 years ago

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:

    • OwenAuger's avatar
      OwenAuger
      Super User
      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