topic Re: Average YTD with summarized data in DAX Commands and Tips

Average YTD with summarized data

Anonymous — Wed, 17 Aug 2022 13:52:34 GMT

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?

Re: Average YTD with summarized data

Jihwan_Kim — Wed, 17 Aug 2022 14:28:38 GMT

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.

Re: Average YTD with summarized data

v-zhangti — Mon, 22 Aug 2022 05:17:14 GMT

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.

Re: Average YTD with summarized data

Anonymous — Mon, 22 Aug 2022 14:22:51 GMT

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

Re: Average YTD with summarized data

Anonymous — Mon, 22 Aug 2022 14:23:21 GMT

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

Re: Average YTD with summarized data

Anonymous — Tue, 23 Aug 2022 12:19:35 GMT

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.