Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Nathaniel_C
Super User
Super User

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


ytde.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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
Not applicable

@Nathaniel_C  I just sent you a message.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors