Average of % Measure for YTD

[Anonymous]

Hello,

I have a table as shown below from the date range 01/01/2019 to 09/01/2019 for the ABC region.  MEASURE A/B % gives me % for each month by using DIVIDE(A,B)

In table 2 I need to create the YTD Average of "A/B%" column.  Since I filtered on Date to show 09/01/2019 I got the recordS for that row and the YTD Average should give an average of "A/B%" from 01/01/2019 to 09/01/2019. Any ideas on how to do that?

In excel I just did by using Average of(A/B%2:E10)

Region	Dates	A	B	A/B %
ABC	1/1/2019	100	80	1.25
ABC	2/1/2019	110	90	1.222222222
ABC	3/1/2019	90	60	1.5
ABC	4/1/2019	80	40	2
ABC	5/1/2019	70	50	1.4
ABC	6/1/2019	60	30	2
ABC	7/1/2019	50	80	0.625
ABC	8/1/2019	95	100	0.95
ABC	9/1/2019	120	150	0.8
Region	Dates	A	B	MEASURE A/B %	YTD Average (MEASURE A/B%)
ABC	9/1/2019	120	150	0.8	1.305246914

[Nathaniel_C]

Hi @Anonymous ,

Added a column in Power Query for the Divide. Then used Summarize to create the new table.


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Table2 =
SUMMARIZE (
    test,
    test[Region],
    "Dates", MAX ( test[Dates] ),
    "Ytd Average", AVERAGE ( test[A/B %] ),
    "A", CALCULATE (
        MAX ( test[A] ),
        FILTER ( test, test[Dates] = MAX ( test[Dates] ) )
    ),
    "B", CALCULATE (
        MAX ( test[B] ),
        FILTER ( test, test[Dates] = MAX ( test[Dates] ) )
    )
)

[Anonymous]

So below is the screenshot of my Pbix

Since I have selected 09/30/2019 (Date is based on End of the Month format), it gives me % calculations for the month Sept. Now I need to create YTD Average % calculation  which should be like these

YTD Avg W/Allowance Over90 % = Average of Current Month W/Allowance Over90 % ( This average should be from Jan to selected month)

YTD Avg Underbill/Overbill%= Average of Current Month Underbill/Overbill% ( This average should be from Jan to selected month)

YTD Avg Cash $ Collected %= Average of Cash $ Collected % ( This average should be from Jan to selected month)

I am using DAX Calculation to determine the current month %

Current Month W/Allowance Over90 % = DIVIDE([Current Month W/Allowance Over 90 $],[Aging],0)

Current Month Underbill/Overbill% = DIVIDE([UnderBill],[OverBill],0)

Current Month Cash $ Collected % = DIVIDE([Collections],[Aging]+[Retention],0)

All the above 3 calculations for Numerator and denominator are DAX calculations and are not available in the dataset.

For example 

OverBill = CALCULATE(SUM(DSO[Value]),DSO[AccountsKey]="OVERBILL")

UnderBill = CALCULATE(SUM(DSO[Value]),DSO[AccountsKey]="UNDERBILL")

Collections = CALCULATE(SUM(DSO[Value]),DSO[AccountsKey]="COLLECTIONS")

Retention = CALCULATE(SUM(DSO[Value]),DSO[AccountsKey]="1-10210")

Any help would be really appreciated.

Thanks,

Fareed.

[Anonymous]

@Nathaniel_C  I just sent you a message.