Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have a table like this below: I have managed to get a total of the Movement Type = "Shipped" and Movement Type = "Received"
How how to I get an average of the total Shipped and Received. Basically, I want a YTD Average shipped and received. Is that possible? Thank you.
Posting Date | Movement Type |
5/31/2021 | Shipped |
6/1/2021 | Shipped |
6/2/2021 | Received |
6/2/2021 | Shipped |
6/2/2021 | Received |
6/3/2021 | Received |
6/3/2021 | Recived |
6/4/2021 | Shipped |
6/5/2021 | Shipped |
Solved! Go to Solution.
Hi, @PowerBIFreak
Create a index column first ,then try measure as below:
YTD_Count =
CALCULATE (
COUNT ( 'Table'[Index] ),
DATESYTD ( 'Table'[Posting Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Movement Type] = MAX ( 'Table'[Movement Type] )
)
)
Total_rows =
CALCULATE (
COUNT ( 'Table'[Index] ),
DATESYTD ( 'Table'[Posting Date] ),
ALL ( 'Table' )
)
YTD Average = [YTD_Count]/[Total_rows]
Please check my pbix file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @PowerBIFreak
Create a index column first ,then try measure as below:
YTD_Count =
CALCULATE (
COUNT ( 'Table'[Index] ),
DATESYTD ( 'Table'[Posting Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Movement Type] = MAX ( 'Table'[Movement Type] )
)
)
Total_rows =
CALCULATE (
COUNT ( 'Table'[Index] ),
DATESYTD ( 'Table'[Posting Date] ),
ALL ( 'Table' )
)
YTD Average = [YTD_Count]/[Total_rows]
Please check my pbix file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
78 | |
75 | |
62 | |
60 | |
47 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |