cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mohammedfareed2 Frequent Visitor
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)

RegionDatesABA/B % 
ABC1/1/2019100801.25 
ABC2/1/2019110901.222222222 
ABC3/1/201990601.5 
ABC4/1/201980402 
ABC5/1/201970501.4 
ABC6/1/201960302 
ABC7/1/201950800.625 
ABC8/1/2019951000.95 
ABC9/1/20191201500.8 
      
      
      
      
RegionDatesABMEASURE A/B %YTD Average (MEASURE A/B%)
ABC9/1/20191201500.81.305246914
3 REPLIES 3
Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: Average of % Measure for YTD

Hi @mohammedfareed2 ,

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


ytde.PNG

 

mohammedfareed2 Frequent Visitor
Frequent Visitor

Re: Average of % Measure for YTD

@Nathaniel_C  I just sent you a message.

mohammedfareed2 Frequent Visitor
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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

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!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)