Pivot table equivalent %of base field

[andyt]

I'm trying to replicate a pivot table in Power Bi which uses a base field and reports a % from that

In excel it is done by using the Show Value as ->% of... and then choose a baase item.

What I'm trying to do is show a series as a % of the first month, ie retained %.

Is this possible?

Thanks

[GilbertQ]

Hi @andyt

This is possible and in some instances it can be done when you are using a table, but if you want to do this in a visual then you have to create a DAX measure.

Below is a blog post explaining how I did this;

https://www.fourmoo.com/2017/07/18/power-bi-dax-measures-for-excel-based-of-column-total-or-of-row-t...

If this is not exactly what you are looking for let us know, with some sample data and I am sure we can get it working.

[andyt]

Thanks for this. It is as part of a matrix, so the % of row and % of column are available already.

What I'm trying to do is the equivalent of % of base field. So if the below is a pivot table, and the % column shows month 2-12 as a % of month 1.

Does that make sense?

Month	%	sum
1	100.0%	186
2	82.3%	153
3	82.3%	153
4	79.0%	147
5	74.7%	139
6	72.0%	134
7	71.5%	133
8	70.4%	131
9	64.5%	120
10	68.3%	127
11	65.6%	122
12	60.8%	113

[GilbertQ]

Hi @andyt

Thanks for more details, I am not 100% sure of the required outcome (Could be a Monday morning thing!)

What should be the desired result off the dataset you provided?

That will help me understand how to get to that result.

[andyt]

Below is the pivot table with payments from customers, so 13566 paid in mnth 1, 9517 in month 2, 9585 in Month 3 etc

Payments	FY
Month	2015/16	2016/17
1	13566	3005
2	9517	2434
3	9585	2187
4	8678	2021
5	8029	1835
6	7496	1666
7	7032	1558
8	6640	1450
9	6295	1370
10	5906	1276
11	5619	1233
12	5386	1143

In excel, to show this as a % of month 1, I use the show values as, %of, based field = Month, Base Item = 1.

this gives me

Retention	FY
Month	2015/16	2016/17
1	100.00%	100.00%
2	70.15%	81.00%
3	70.65%	72.78%
4	63.97%	67.25%
5	59.18%	61.06%
6	55.26%	55.44%
7	51.84%	51.85%
8	48.95%	48.25%
9	46.40%	45.59%
10	43.54%	42.46%
11	41.42%	41.03%
12	39.70%	38.04%

So for example, i can see of those customers who made a payment in month 1, 55.26% paid in month 6.

Does that help?

Thanks for your time

[GilbertQ · Accepted solution]

Hi @andyt

I think I have managed to solve your issue, as long as you keep on using the Month Numbers.

What I did was to change the shape of your data so that it made it easier to work with, which is something that is very common when designing datasets.

Then what I did was to create the following measures below.

// This is the default measure to get the amounts
Sales = SUM(Table1[Amount])

//This measure then only gets the Value where it is equal to Month 1
Month 1 Value = CALCULATE([Sales],Table1[Month] = 1)

//This measure then does the calculation for the % of Base Field.
% of Base Value = DIVIDE([Sales],[Month 1 Value])

To better explain what the measure [Month 1 Value] is doing, there is an image below, which you can then see because I hard coded the Sales for Month 1, it will repeat across all the data.

So once I got the above the final measure then dropped into place, and I got the same results as with your sample.

I have also got a link for the Power BI Desktop file.

https://1drv.ms/u/s!Apxn-69XhcAmhr5lR4KaCo8YNudFSQ

[andyt]

Thanks for that. That has done the job for me.

[GilbertQ]

Hi @andyt

Awesome glad we found a solution