Power Bi , Average constant values in column rather than rolling average?

0
votes

Sorry I'm lost I really appreciate if you can help me calculate the follwoing

So I've a table two fy data LY(Last Year) FY2020 and (This Year) or FY2021, 

 I would like to compare this year we spent on Agency vs Avg Spent on LY

Agency spend vs last year average; expressed as % of total people costs spend in month

The table output is like

                       2020                                              2021

F_Month  AgencyPaid2020  msrAgencyPayPrv  AgencyPay        msrAgencyPayPrv

1                217922                  18161                  37930                 18161

2                296460                   24705                56155                  24705

3                298863                  24905                     0                       24905

.                   xxxx                     xxxxx                       0                       xxxx

.

12               110166                   9181                      0           

---             ---------              ------------         -------------         --------------

Total         169955                 141630                18225                      141630

I'm expecting 141630 should show in all rows for above table(Col2) but when I drag the measure into table its calculating differently 

The measure I've calculated is 

> msrAgencyPayPrvYearAvg =
> CALCULATE(SUM(Data[IM_Actual_Positive]),Data[EBITDA]="2
> Pay",Data[PayCat_1]="Agency",Data[int_fyear]=2020)/12'''

Thanks for you help

The 4th column should not show 2021 is there a way I can hide as its coming because in the 'Visualization' under column I've put 'int_fyear' column

and vales I've (Agency Pay and msrAvgAgencyPay) , sorry I 've lost the plot

Kind regards,

Farhan

1
powerbicomparisonaveragedax
Measure = VAR MaxTS = CALCULATE ( MAX(Sheet1[Actual]), FILTER ( ALLEXCEPT(Sheet1,Sheet1[Actual]), Sheet1[FY]="2020") ) RETURN CALCULATE ( MAX(Sheet1[Actual]), FILTER(ALLEXCEPT(Sheet1,Sheet1[Actual,]),Sheet1[Actual]=MaxTs ) ) – F.Syed

1 Answers

0
votes

I've found the answer I should use the following function

msrAgencyPaySheet = CALCULATE(Sum(Sheet1[Actual]),filter(ALLSELECTED(Sheet1), Sheet1[FY]=2020))/12