Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
webportal
Impactful Individual
Impactful Individual

Calculate total YTD within filtered table

This is MyTable:

 

webportal_0-1615835617680.png

 

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:

 

webportal_2-1615836214711.png

 

Measure =
VAR Tab = FILTER(...)

RETURN

TOTALYTD(....)

2 ACCEPTED SOLUTIONS
MFelix
Super User
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:

MFelix_0-1615992892136.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

v-yiruan-msft
Community Support
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] )
        )
    )

 

yingyinr_0-1616062063790.png

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.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
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] )
        )
    )

 

yingyinr_0-1616062063790.png

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.
MFelix
Super User
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:

MFelix_0-1615992892136.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.