Hi Community,
Could you please help me finding cummulative emission with Filter condition
I want to create a measure that can calculate emission up till Forecast value is "N". I am trying with following measure but I am still getting values where forecast is "Y". so, my goal is create measure that calculate cummulative total up till Forecast value is "N" and rest is blank. I guess it is happenig becasue of ALL.
My expected results should look like this
Thankyou in Anticipation
Solved! Go to Solution.
@sam_hoccane , Try one of the two
Actual Cumulative Emissions =
VAR _max = maxx(filter(allselected(data) ,data[Forecast]="N"), data[Date])
VAR _cumulative_before_forecast = CALCULATE( SUM(data[Emission]) , FILTER ( ALLSELECTED(data), data[Date] <= MAX( data[Date]) && data[Date] <=_max && data[Forecast]="N"))
Return _cumulative_before_forecast
Actual Cumulative Emissions =
VAR _max = maxx(filter(allselected(data) ,data[Forecast]="N"), data[Date])
VAR _cumulative_before_forecast = if( max(data[Date]) <=_max ,CALCULATE( SUM(data[Emission]) , FILTER ( ALLSELECTED(data), data[Date] <= MAX( data[Date]) && data[Forecast]="N")), blank())
Return _cumulative_before_forecast
Hi @sam_hoccane ,
I have created a simple sample, please refer to my pbix file to see if it help you.
Create a measure.
Measure =
VAR _result =
CALCULATE (
SUM ( 'Table'[Emission] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] <= SELECTEDVALUE ( 'Table'[date] )
&& 'Table'[Forecasst] = "N"
)
)
RETURN
IF ( MAX ( 'Table'[Forecasst] ) = "N", _result, BLANK () )
Or a column.
Column =
VAR _result =
CALCULATE (
SUM ( 'Table'[Emission] ),
FILTER (
( 'Table' ),
'Table'[date] <= EARLIER ( 'Table'[date] )
&& 'Table'[Forecasst] = "N"
)
)
RETURN
IF ( 'Table'[Forecasst] = "N", _result, BLANK () )
If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output with more details.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sam_hoccane ,
I have created a simple sample, please refer to my pbix file to see if it help you.
Create a measure.
Measure =
VAR _result =
CALCULATE (
SUM ( 'Table'[Emission] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] <= SELECTEDVALUE ( 'Table'[date] )
&& 'Table'[Forecasst] = "N"
)
)
RETURN
IF ( MAX ( 'Table'[Forecasst] ) = "N", _result, BLANK () )
Or a column.
Column =
VAR _result =
CALCULATE (
SUM ( 'Table'[Emission] ),
FILTER (
( 'Table' ),
'Table'[date] <= EARLIER ( 'Table'[date] )
&& 'Table'[Forecasst] = "N"
)
)
RETURN
IF ( 'Table'[Forecasst] = "N", _result, BLANK () )
If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output with more details.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@sam_hoccane , Try one of the two
Actual Cumulative Emissions =
VAR _max = maxx(filter(allselected(data) ,data[Forecast]="N"), data[Date])
VAR _cumulative_before_forecast = CALCULATE( SUM(data[Emission]) , FILTER ( ALLSELECTED(data), data[Date] <= MAX( data[Date]) && data[Date] <=_max && data[Forecast]="N"))
Return _cumulative_before_forecast
Actual Cumulative Emissions =
VAR _max = maxx(filter(allselected(data) ,data[Forecast]="N"), data[Date])
VAR _cumulative_before_forecast = if( max(data[Date]) <=_max ,CALCULATE( SUM(data[Emission]) , FILTER ( ALLSELECTED(data), data[Date] <= MAX( data[Date]) && data[Forecast]="N")), blank())
Return _cumulative_before_forecast
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!