Forum Discussion
Dynamic Measure based on filter
Hi ignas
Here is one way of doing it.
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
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
Hi ignas
Here is one way of doing it.
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