cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ignas Regular Visitor
Regular Visitor

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.

Capture.PNG

 

Capture2.PNG

 

 

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

 3.PNG

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

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

 


Regards,

 

Ignas

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Dynamic Measure based on filter

Hi @ignas

 

Here is one way of doing it.

 

PBIX link

 

Set up the data model like this:image.png

 

 

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 Smiley Happy

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




4 REPLIES 4
alexei7 Member
Member

Re: Dynamic Measure based on filter

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 Regular Visitor
Regular Visitor

Re: Dynamic Measure based on filter

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:

3.PNG

ignas Regular Visitor
Regular Visitor

Re: Dynamic Measure based on filter

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:

 

 

4.PNG

 

Here is a new file:

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

Super User
Super User

Re: Dynamic Measure based on filter

Hi @ignas

 

Here is one way of doing it.

 

PBIX link

 

Set up the data model like this:image.png

 

 

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 Smiley Happy

 

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!