cancel
Showing results for
Did you mean:
mohammedfareed2 Frequent Visitor

## Average of % Measure for YTD

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
3 REPLIES 3
Highlighted
Nathaniel_C Super Contributor

## Re: Average of % Measure for YTD

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] ) )
)
)`````` mohammedfareed2 Frequent Visitor

## Re: Average of % Measure for YTD

@Nathaniel_C  I just sent you a message.

mohammedfareed2 Frequent Visitor

## Re: Average of % Measure for YTD

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.

Announcements #### New Topics Started Badges Coming #### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there! #### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020 Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,262)