cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Impactful Individual

## Calculate total YTD within filtered table

This is MyTable:

It is defined in a variable within a calculated table, like:

Table=
VAR Tab = FILTER(...)
RETURN
Tab

I want a measure with the YTD values sum of Value, but ONLY for the ID's whose annual sum is negative.

So, the total sum of 123 = 33.

The total sum of 456 = 129

And the total sum of 789 = -205

So, I should get the following measure:

Measure =
VAR Tab = FILTER(...)

RETURN

TOTALYTD(....)

2 ACCEPTED SOLUTIONS
Super User

Hi  @webportal ,

Try the following code:

``````Measure
=
VAR temp_table =
CALCULATETABLE (
FILTER (
SUMMARIZE (
ALL ( 'Table'[ID] ),
'Table'[ID],
"Total_Value", SUM ( 'Table'[Value] )
),
[Total_Value] < 0
),
ALL ( 'Table' )
)
VAR IDSELECTION =
SELECTCOLUMNS ( temp_table, "ID", 'Table'[ID] )
RETURN
CALCULATE (
TOTALYTD ( SUM ( 'Table'[Value] ), 'Table'[Date] ),
FILTER ( ALL ( 'Table'[ID] ), 'Table'[ID] IN IDSELECTION )
)``````

Result below and in attach PBIX file:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Community Support

Hi @webportal ,

You can create a measure as below:

``````Measure =
VAR _tab =
SUMMARIZE (
'Table',
'Table'[ID],
"svalue", CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
)
VAR _tvalue =
MAXX ( FILTER ( _tab, [svalue] < 0 ), [ID] )
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[ID] = _tvalue
&& 'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
)
)``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

Hi @webportal ,

You can create a measure as below:

``````Measure =
VAR _tab =
SUMMARIZE (
'Table',
'Table'[ID],
"svalue", CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
)
VAR _tvalue =
MAXX ( FILTER ( _tab, [svalue] < 0 ), [ID] )
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[ID] = _tvalue
&& 'Table'[Date] <= SELECTEDVALUE ( 'Table'[Date] )
)
)``````

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

Hi  @webportal ,

Try the following code:

``````Measure
=
VAR temp_table =
CALCULATETABLE (
FILTER (
SUMMARIZE (
ALL ( 'Table'[ID] ),
'Table'[ID],
"Total_Value", SUM ( 'Table'[Value] )
),
[Total_Value] < 0
),
ALL ( 'Table' )
)
VAR IDSELECTION =
SELECTCOLUMNS ( temp_table, "ID", 'Table'[ID] )
RETURN
CALCULATE (
TOTALYTD ( SUM ( 'Table'[Value] ), 'Table'[Date] ),
FILTER ( ALL ( 'Table'[ID] ), 'Table'[ID] IN IDSELECTION )
)``````

Result below and in attach PBIX file:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors