Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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
Here is the dataset for your reference:
https://drive.google.com/file/d/1t6tqr_zzfBWnr5odVpVUQAk40eH3SFO7/view?usp=sharing
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |