How to create measures based on pivot table in powerBI?

[Cze-Jhin]

Hi All,

Would like to ask if it is possible to create measures based on a pivot table generated in PowerBI? 

Here is a pivot table generated via powerBI:

Attempt	Outcome A	Outcome B	Outcome C	Outcome D	Outcome E
1	14236	14757	533	5678	9090
2	12644	11493	252	3456	8744
3	11865	9100	119	23	7654
4	11299	7962	73	123	6543
5	11152	6682	69	5677	4321
6	1879	6380	37	907	321
7	1749	5545	29	77	21
8	1479	4261	24	54	1
9	1203	2439	13	2	1
10	149	443	1	1	1

Based on the pivot table above, I need to create four measures, analysing by rows:

Attempt	Outcome A	Outcome B	Outcome C	Outcome D	Outcome E		Measure A	Measure B	Measure C	Measure D
1	14236	14757	533	5678	9090		-74.06%	387.28%	-522.94%	25.82%
2	12644	11493	252	3456	8744		-29.24%	306.33%	-1047.52%	31.40%
3	11865	9100	119	23	7654		34.27%	232.20%	677.57%	36.26%
4	11299	7962	73	123	6543		48.01%	214.05%	445.88%	33.63%
5	11152	6682	69	5677	4321		-29.53%	259.41%	-878.44%	18.32%
6	1879	6380	37	907	321		-1696.26%	101.24%	-5.97%	3.49%
7	1749	5545	29	77	21		-18580.95%	81.41%	-0.44%	0.28%
8	1479	4261	24	54	1		-286000.00%	64.00%	-0.02%	0.02%
9	1203	2439	13	2	1		-125100.00%	40.23%	-0.03%	0.03%
10	149	443	1	1	1		-29600.00%	6.53%	-0.02%	0.17%

The formula for the four measures in excel are: 

Measure A: =IFERROR((B2-C2-D2-E2)/F2,0)

Measure B: =IFERROR(SUM(B2,C2,D2,E2)/$F$2,0)

Measure C: =IFERROR(I2/H2,0)

Measure D =IFERROR(F2/SUM(B2,C2,D2,E2),0)

May I know how can I create the measures based on the pivot table? Or should I create a new table for it?

Hope you can help me with this, would really appreciate it, thank you very much.

[v-janeyg-msft]

Hi, @Cze-Jhin 

Yes, you can create it, Like this:

MeasureA = IFERROR(DIVIDE(MAX('Table'[Outcome A])-MAX('Table'[Outcome B])-MAX('Table'[Outcome C])-MAX('Table'[Outcome D]),MAX('Table'[Outcome E])),0)

MeasureB = var a=CALCULATE(MAX('Table'[Outcome E]),'Table'[Attempt]=1)
return IFERROR(DIVIDE(max('Table'[Outcome A])+MAX('Table'[Outcome B])+MAX('Table'[Outcome C])+MAX('Table'[Outcome D]),a),0)

MeasureC = IFERROR(DIVIDE([MeasureB],[MeasureA]),0)

MeasureD = IFERROR(DIVIDE(MAX('Table'[Outcome E]),MAX('Table'[Outcome A])+MAX('Table'[Outcome B])+MAX('Table'[Outcome C])+MAX('Table'[Outcome D])),0)

Did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards

Janey Guo

[Cze-Jhin]

Hi @v-janeyg-msft , thank you so much for the help. May I know if the dataset need to calculate an extra measure based on the total of Outcome by Attempt as display below:

Attempt	Outcome A	Outcome B	Outcome C	Outcome D	Outcome E	Grand Total		Measure A	Measure B	Measure C	Measure D	Measure E
1	14236	14757	533	5678	9090	44294		-74.06%	387.28%	-522.94%	25.82%	34.54%
2	12644	11493	252	3456	8744	36589		-29.24%	306.33%	-1047.52%	31.40%	34.03%
3	11865	9100	119	23	7654	28761		34.27%	232.20%	677.57%	36.26%	27.11%
4	11299	7962	73	123	6543	26000		48.01%	214.05%	445.88%	33.63%	25.92%
5	11152	6682	69	5677	4321	27901		-29.53%	259.41%	-878.44%	18.32%	36.08%
6	1879	6380	37	907	321	9524		-1696.26%	101.24%	-5.97%	3.49%	13.28%
7	1749	5545	29	77	21	7421		-18580.95%	81.41%	-0.44%	0.28%	1.71%
8	1479	4261	24	54	1	5819		-286000.00%	64.00%	-0.02%	0.02%	1.36%
9	1203	2439	13	2	1	3658		-125100.00%	40.23%	-0.03%	0.03%	0.44%
10	149	443	1	1	1	595		-29600.00%	6.53%	-0.02%	0.17%	0.50%
TOTAL	67655	69062	1150	15998	36697	190562

Measure E formula is as such: =IFERROR((G2-B2-C2)/G2,0)

Across the no. of attempt, the grand total value and measure E formula is different, such as for attempt 1 the formula is =IFERROR((G2-B2-C2)/G2,0), attempt 2 is =IFERROR((G3-B3-C3)/G3,0), attempt 3 is =IFERROR((G4-B4-C4)/G4,0) and so on.

May I know how to create that Measure E? I tried to use 

CALCULATE(COUNTROWS(Sheet1), ALL(Sheet1[Attempt])), but it divides 190562 instead of the grand total of each attempt.

Here is the dataset for your reference:

https://drive.google.com/file/d/1t6tqr_zzfBWnr5odVpVUQAk40eH3SFO7/view?usp=sharing

How you can help me with it. Thank you very much.

[v-janeyg-msft · Accepted solution]

Hi, @Cze-Jhin 

Please try:

MeasureE = var a=MAX('Table'[Outcome A])+MAX('Table'[Outcome B])+MAX('Table'[Outcome C])+MAX('Table'[Outcome D])+MAX('Table'[Outcome E])
return IFERROR(DIVIDE(a-MAX('Table'[Outcome A])-MAX('Table'[Outcome b]),a),0)

Did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards

Janey Guo