Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
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] ) )
)
)
Proud to be a Super User!
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 %
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |