Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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]
    )

TeigeGao's avatar
TeigeGao
Solution Sage
7 years ago

Hi Anonymous ,

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:

Best Regards,

Teige

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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]
      )