cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
awotsn Regular Visitor
Regular Visitor

Running Average on a Measure

I need help. I have attached a pbix file. in the file, i would like to create the Table[Measure 2] directly in the Sheet1 table without having to create the calculated table "Table". This is becuase i want to be able to slice and dice the data by vintage, Portfolio, etc.

 

I will really appreciate if someone can help me out. Thanks.   pbix file

1 ACCEPTED SOLUTION

Accepted Solutions
awotsn Regular Visitor
Regular Visitor

Re: Running Average on a Measure

@TeigeGao  Oh! I finally got it! Yay!

 

try =
AVERAGEX (
    CALCULATETABLE (
        GROUPBY (
            'Sheet1',
            'Sheet1'[Loan Life],
            'Sheet1'[Vintage],
            "Denomi"SUMX ( CURRENTGROUP (), 'Sheet1'[ Denominator Column] )
        ),
        FILTER (
            ALLSELECTED ( Sheet1[Loan Life] ),
            ISONORAFTER ( Sheet1[Loan Life], MAX ( Sheet1[Loan Life] )DESC )
        )
    ),
    [Denomi]
)

3 REPLIES 3
TeigeGao Super Contributor
Super Contributor

Re: Running Average on a Measure

Hi @awotsn ,

After analysizing your pbix file, my understanding is that you want to average the "Denominator Column" based on the group of "Vintage" and "Loan Life", in this scenario, we can create a measure like below:

Measure =
CALCULATE (
    AVERAGE ( Sheet1[ Denominator Column] ),
    FILTER (
        ALL ( Sheet1[Vintage], Sheet1[Loan Life] ),
        Sheet1[Vintage] = MIN ( Sheet1[Vintage] )
            && Sheet1[Loan Life] = MIN ( Sheet1[Loan Life] )
    )
)

The result will like below:

Snipaste_2019-04-23_15-32-14.png

Best Regards,

Teige

awotsn Regular Visitor
Regular Visitor

Re: Running Average on a Measure

Thanks for responding @TeigeGao  . The result should be the values in Measure 2 before filters/slicers are applied

Capture.JPG

awotsn Regular Visitor
Regular Visitor

Re: Running Average on a Measure

@TeigeGao  Oh! I finally got it! Yay!

 

try =
AVERAGEX (
    CALCULATETABLE (
        GROUPBY (
            'Sheet1',
            'Sheet1'[Loan Life],
            'Sheet1'[Vintage],
            "Denomi"SUMX ( CURRENTGROUP (), 'Sheet1'[ Denominator Column] )
        ),
        FILTER (
            ALLSELECTED ( Sheet1[Loan Life] ),
            ISONORAFTER ( Sheet1[Loan Life], MAX ( Sheet1[Loan Life] )DESC )
        )
    ),
    [Denomi]
)