Forum Discussion

Olia's avatar
Olia
Icon for Advocate II rankAdvocate II
7 years ago
Solved

Rolling average with conditions

Folks, could you please assist me with this?   I have very messy data, which contains 3 columns: 1) FA 2) VERSION 3) SUM(AMOUNT) 4) FSI (=financial statement item number)   what makes things ...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi Olia,

    Based on my test, you could refer to below formula to calculate a table meets the requirements that 'FA is a number' and VERSION=CCA':

    Table = CALCULATETABLE('Sheet2',FILTER(Sheet2,'Sheet2'[VERSION]="CCA"&&UNICODE('Sheet2'[FA])<65))

    Result:

    But I could not understand how do you want to calculate the average Actual $$$ per FSI? Could you please offer me more information if possible? You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

     

     

v-danhe-msft's avatar
v-danhe-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Olia,

Based on my test, you could refer to below formula to calculate a table meets the requirements that 'FA is a number' and VERSION=CCA':

Table = CALCULATETABLE('Sheet2',FILTER(Sheet2,'Sheet2'[VERSION]="CCA"&&UNICODE('Sheet2'[FA])<65))

Result:

But I could not understand how do you want to calculate the average Actual $$$ per FSI? Could you please offer me more information if possible? You could also download the pbix file to have a view.

 

Regards,

Daniel He