Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table with the columns "ID" ,"month" , "score" and "publish date". I want to first create a measure that gets me the "average score per publish date" and then use the values from the measure to get the average score per month.
Here's a table as an example
ID | Score | Publish Date |
1 | 90 | 9/14/2022 |
1 | 60 | 9/14/2022 |
1 | 75 | 9/15/2022 |
1 | 85 | 9/15/2022 |
What I want but do not know how to do.
Step 1. Averge score per publish date 9/14/2022
90+60 = 150/2= 75
Step 2. Average score per publish date 9/15/2022
85+75= 160/2 = 80
Step 3. Average score per month. ( using average score per publish date to calculate)
80+75= 155/2 = 77.5
What I know how to do but do not want.
Step 1. Average score per month( using scores to caluclate)
90+60+85+75 = 77.5
Solved! Go to Solution.
The below measure should work across any date range - week, month, quarter etc
Avg Score =
AVERAGEX (
ADDCOLUMNS (
VALUES ( 'Date'[Date] ),
"@val", CALCULATE ( AVERAGE ( 'Table'[Score] ) )
),
[@val]
)
Hi, @EMassicot ;
You could create measures as follow:
1.
average score per publish date = CALCULATE( AVERAGE('Table'[Score]),ALLEXCEPT('Table','Table'[Publish Date]))
2.
average score per month = AVERAGEX(SUMMARIZE(ALL('Table'),[Publish Date],"1",[average score per publish date]),[1])
average score per month2 = CALCULATE(AVERAGE('Table'[Score]),FILTER(ALL('Table'),EOMONTH([Publish Date],0)=EOMONTH(MAX('Table'[Publish Date]),0)))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have a table with the columns "ID" ,"month" , "score" and "publish date".
What I ultimately want, is to get the avearge score per each publish date using a DAX formula.
Here's a table as an example
ID | Score | Publish Date | Month |
1 | 90 | 9/14/2022 | September |
1 | 60 | 9/14/2022 | September |
1 | 75 | 9/15/2022 | September |
1 | 85 | 9/15/2022 | September |
What I want but do not know how to do.
Step 1. Averge score per publish date 9/14/2022
90+60 = 150/2= 75
Step 2. Average score per publish date 9/15/2022
85+75= 160/2 = 80
Here is also the DAX formula that I tried using but it's not giving me exactly what I want.
@EMassicot Put Publish Date in a table visual. Use a simple Average aggregation on Score.
Hi, @EMassicot ;
You could create measures as follow:
1.
average score per publish date = CALCULATE( AVERAGE('Table'[Score]),ALLEXCEPT('Table','Table'[Publish Date]))
2.
average score per month = AVERAGEX(SUMMARIZE(ALL('Table'),[Publish Date],"1",[average score per publish date]),[1])
average score per month2 = CALCULATE(AVERAGE('Table'[Score]),FILTER(ALL('Table'),EOMONTH([Publish Date],0)=EOMONTH(MAX('Table'[Publish Date]),0)))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The below measure should work across any date range - week, month, quarter etc
Avg Score =
AVERAGEX (
ADDCOLUMNS (
VALUES ( 'Date'[Date] ),
"@val", CALCULATE ( AVERAGE ( 'Table'[Score] ) )
),
[@val]
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |