Forum Discussion
Average YTD with summarized data
I am attempting to show average ytd (Jan shows Jan, Feb shows av(Jan,Feb), Mar shows av(Jan,Feb,Mar)...) in a line graph, but I cannot figure it out.
I have east coast and west coast data.
Each instance has a quantity associated with it, but I need the calculation to be a percent: east/total, summarized, because each instance would simply be 1 or 0.
I have month start date.
Any tips?
5 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like the attached.
Please check the attached pbix file. All measures are in the attached pbix file.
I hope the below can provide some ideas on how to create solutions for your dataset.
Didn't quite work. Date field not as robust. Sample data:
NEED: average ytd quantity east as a percent of total quantity by period, (for month 1: av(month 1), for month 2: av(month1 and month 2)….
Month Periodic Month Deadline Date Total Quantity Quantity Location Quantity East Monthly Average of East/Total YTD% 1 1/12/2022 18823 East 18823 1 1/12/2022 1243 East 1243 1 1/28/2022 465743 East 465743 1 1/28/2022 24352 West 0 2 2/10/2022 23425 West 0 2 2/10/2022 254254 East 254254 2 2/10/2022 684 East 684 2 2/10/2022 3457 West 0 2 2/19/2022 34 West 0 2 2/19/2022 757333 East 757333 2 2/19/2022 3456 West 0 2 2/28/2022 23525 East 23525 2 2/28/2022 757 West 0 2 2/28/2022 346 West 0 3 3/4/2022 377658 West 0 3 3/4/2022 2654 West 0 3 3/4/2022 16382 East 16382 3 3/19/2022 618647 East 618647 4 4/4/2022 984 West 0 4 4/4/2022 6115 West 0 4 4/4/2022 6184 West 0 4 4/4/2022 4968136 East 4968136 4 4/19/2022 4984 East 4984 4 4/19/2022 13854 East 13854 4 4/19/2022 1683 East 1683 4 4/30/2022 968413 West 0 4 4/30/2022 6814 East 6814 5 5/2/2022 8641 West 0 5 5/2/2022 384341 West 0 5 5/2/2022 4684 East 4684 5 5/2/2022 168616 West 0 5 5/12/2022 6484 East 6484 5 5/12/2022 165 West 0 5 5/12/2022 648 West 0 Hi Jihwan,
This solution is close but not quite right. I feel like there is an issue with the measure titled "quantity measure:" as I am not sure how that one is being filtered to return only East values, if at all. When I return the ratio measure, I get periodic east/total, but cannot yet year to date averages.
v-zhangtiCommunity Support
Hi, Anonymous
Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Didn't quite work. Date field not as robust. Sample data:
NEED: average ytd quantity east as a percent of total quantity by period, (for month 1: av(month 1), for month 2: av(month1 and month 2)….
Month Periodic Month Deadline Date Total Quantity Quantity Location Quantity East Monthly Average of East/Total YTD% 1 1/12/2022 18823 East 18823 1 1/12/2022 1243 East 1243 1 1/28/2022 465743 East 465743 1 1/28/2022 24352 West 0 2 2/10/2022 23425 West 0 2 2/10/2022 254254 East 254254 2 2/10/2022 684 East 684 2 2/10/2022 3457 West 0 2 2/19/2022 34 West 0 2 2/19/2022 757333 East 757333 2 2/19/2022 3456 West 0 2 2/28/2022 23525 East 23525 2 2/28/2022 757 West 0 2 2/28/2022 346 West 0 3 3/4/2022 377658 West 0 3 3/4/2022 2654 West 0 3 3/4/2022 16382 East 16382 3 3/19/2022 618647 East 618647 4 4/4/2022 984 West 0 4 4/4/2022 6115 West 0 4 4/4/2022 6184 West 0 4 4/4/2022 4968136 East 4968136 4 4/19/2022 4984 East 4984 4 4/19/2022 13854 East 13854 4 4/19/2022 1683 East 1683 4 4/30/2022 968413 West 0 4 4/30/2022 6814 East 6814 5 5/2/2022 8641 West 0 5 5/2/2022 384341 West 0 5 5/2/2022 4684 East 4684 5 5/2/2022 168616 West 0 5 5/12/2022 6484 East 6484 5 5/12/2022 165 West 0 5 5/12/2022 648 West 0
