Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |