The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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?
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 |
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.
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.
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 |
User | Count |
---|---|
15 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
8 | |
8 |