cancel
Showing results for
Did you mean:
Frequent Visitor

How to create measures based on pivot table in powerBI?

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.

1 ACCEPTED SOLUTION
Community Support

Hi, @Cze-Jhin

``````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)``````

Best Regards

Janey Guo

3 REPLIES 3
Community Support

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)``

Best Regards

Janey Guo

Frequent Visitor

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.

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

Hi, @Cze-Jhin

``````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)``````

Best Regards

Janey Guo

Announcements

Launching new user group features

Learn how to create your own user groups today!