Power Pivot - working with daily percentages

[Escape777]

Hi all,

I am fairly new to query and power pivot, but I have now been able to consolidate a couple of Excel data tables into a single database, using power query. Using power pivot I am able to run a daily report, as well as a monthly report. The daily reports have an actual percentage in the data. In the Power pivot table, once I filter per month, it wants to naturally either sum or average these values, which does not make sense, I am interested in the last value for the month, and the difference between this and the previous end of month value. i.e. the percentage change over the month.

I have tried using a calculated field to get the value for the last reported value for the month, but I am completely hopeless with DAX formulas for now...

My two questions are:

1. How can I get Power pivot to consolidate the percentages in the month filter, into the last value of the month?
2. If I need to do this outside the power pivot report, then how do I pull the correct val
3. ues out of the power pivot into a dashboard report?

 

[Escape777]

Just a clarifying note on my previous post.

I am using a date table, with the data summarised per month. The idea would be that the percentages would summarise to the last recorded value in the month, while the rest of the data should be totalled together, as it is currently doing. I then want to use this number to calculate the difference between the two values at the end of the two adjacent months.

 

[alansidman]

It would be very helpful if you showed us some data you are trying to pivot and manually mock up what the expected output should look like. Please use XL2BB to upload your data. Trying to visualize what you are asking can be eased with examples that actually show what you have. Not entirely sure, but you may need to build a measure to get what you want. Will know better once we see your examples.

 

[Escape777]

Hi Alan,

Thank you for the pointer. I hope this sheet provides the explanation that is required. In short, you can not sum percentages, but you can total numbers.



Regards,

Werner

Total Mine Balance Messed up Example Mr Excel .xlsx
	A	B	C	D	E	F	G	H	I	J	K
15	Row Labels	Sum of Purchased Water Volume	Sum of Disposed Water	% Process Water Dam Level	Sum of Rain Water (mm)
16	Jun
17	6/26/2020	4500	5800	50%	6
18	6/27/2020	4410	5800	82%	2
19	6/28/2020	4410	5916	65%	7
20	6/29/2020	4365	5974	82%	3
21	6/30/2020	4410	5858	60%	9
22	Jul						Current Answer	22095	29348	340%	27.6
23	7/1/2020	4500	5800	81%	9		Required Answer	22095	29348	60%	27.6
24	7/2/2020	4410	5916	57%	7
25	7/3/2020	4680	6032	23%	1
26	7/4/2020	4590	5626	67%	5
27	7/5/2020	4725	5568	59%	0
28	7/6/2020	4455	5800	89%	7
29	7/7/2020	4590	6090	86%	8
30	7/8/2020	4275	6032	98%	6
31	7/9/2020	4365	5742	8%	2
32	7/10/2020	4320	5974	78%	8
33	7/11/2020	4590	5568	77%	8
34	7/12/2020	4365	5800	85%	1
35	7/13/2020	4410	6090	80%	7
36	7/14/2020	4500	6032	86%	6
37	7/15/2020	4320	5510	36%	6
38	7/16/2020	4680	6032	0%	2
39	7/17/2020	4500	5568	73%	8
40	7/18/2020	4320	5510	78%	2
41	7/19/2020	4410	5742	47%	7
42	7/20/2020	4365	5742	29%	5
43	7/21/2020	4410	6032	20%	9
44	7/22/2020	4410	5974	78%	2
45	7/23/2020	4365	5916	37%	7
46	7/24/2020	4680	6090	14%	6
47	7/25/2020	4635	5974	39%	3
48	7/26/2020	4320	5510	74%	10
49	7/27/2020	4275	5858	47%	9
50	7/28/2020	4365	5568	63%	7
51	7/29/2020	4320	5916	41%	4
52	7/30/2020	4320	5510	34%	10
53	7/31/2020	4275	5916	81%	2
54	Aug						Current Answer	137745	28768	266%	32.5
55	8/1/2020	4365	5916	79%	1		Required Answer	137745	180438	81%	175
56	8/2/2020	4320	5568	20%	5
57	8/3/2020	4410	5974	38%	0
58	8/4/2020	4545	6090	41%	2
59	8/5/2020	4635	5800	68%	8
60	8/6/2020	4500	5742	31%	9
61	8/7/2020	4590	5858	69%	2
62	8/8/2020	4500	5858	85%	0
63	8/9/2020	4590	5800	87%	9
64	8/10/2020	4635	5510	17%	5
65	8/11/2020	4725	5858	60%	9
66	8/12/2020	4680	5742	90%	0
67	8/13/2020	4455	5684	65%	5
68	8/14/2020	4275	5916	11%	3
69	8/15/2020	4725	5858	65%	8
70	8/16/2020	4500	6032	6%	5
71	8/17/2020	4500	5568	59%	2
72	8/18/2020	4545	5800	63%	4
73	8/19/2020	4500	6032	29%	9
74	8/20/2020	4320	5974	24%	8
75	8/21/2020	4365	5684	92%	0
76	8/22/2020	4635	6032	18%	0
77	8/23/2020	4590	5916	48%	5
78	8/24/2020	4635	5858	88%	7
79	8/25/2020	4455	5742	47%	1
80	8/26/2020	4365	5510	99%	0
81	8/27/2020	4725	5858	92%	3
82	8/28/2020	4365	5684	22%	8
83	8/29/2020	4320	5626	27%	9
84	8/30/2020	4320	5568	48%	3
85	8/31/2020	4680	5568	39%	1
86	Grand Total	299610	389412	3737%	334		Current Answer	139770	28304	229%	23.9
87							Required Answer	139770	179626	81%	131
88
89
90
91
92							Dashboard Answers
93							Sum of Purchased Water Volume	Sum of Disposed Water	% Process Water Dam Level	Sum of Rain Water (mm)	Accumulation or Depletion
94						June	22095	29348	60%	28
95						July	137745	180438	81%	175	22%
96						August	139770	179626	81%	131	0%
Consolidated Database

Cell Formulas
Range	Formula
H22:K22,I86:K86,I54:K54	H22	=SUM(B17:B21)
H23:I23,K23	H23	=SUM(B17:B21)
J23	J23	=GETPIVOTDATA("[Measures].[Sum of Process Water Pond Level]",$A$15,"[Table1].[Date]","[Table1].[Date].&[2020-06-30T00:00:00]","[Table1].[Date (Month)]","[Table1].[Date (Month)].&[Jun]")
H54,H86	H54	=SUM(B23:B53)
H55:I55,K55,H87:I87,K87	H55	=SUM(B23:B53)
J55,J87	J55	=GETPIVOTDATA("[Measures].[Sum of Process Water Pond Level]",$A$15,"[Table1].[Date]","[Table1].[Date].&[2020-07-31T00:00:00]","[Table1].[Date (Month)]","[Table1].[Date (Month)].&[Jul]")
G94:J94	G94	=H23
G95:J95	G95	=H55
K95:K96	K95	=I95-I94
G96:J96	G96	=H87