cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cze-Jhin
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:

AttemptOutcome AOutcome BOutcome COutcome DOutcome E
1142361475753356789090
2126441149325234568744
3118659100119237654
4112997962731236543
51115266826956774321
61879638037907321
717495545297721
81479426124541
9120324391321
10149443111

 

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

AttemptOutcome AOutcome BOutcome COutcome DOutcome E Measure AMeasure BMeasure CMeasure D
1142361475753356789090 -74.06%387.28%-522.94%25.82%
2126441149325234568744 -29.24%306.33%-1047.52%31.40%
3118659100119237654 34.27%232.20%677.57%36.26%
4112997962731236543 48.01%214.05%445.88%33.63%
51115266826956774321 -29.53%259.41%-878.44%18.32%
61879638037907321 -1696.26%101.24%-5.97%3.49%
717495545297721 -18580.95%81.41%-0.44%0.28%
81479426124541 -286000.00%64.00%-0.02%0.02%
9120324391321 -125100.00%40.23%-0.03%0.03%
10149443111 -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

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)

vjaneygmsft_0-1626676909521.png

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

 

Best Regards

Janey Guo

View solution in original post

3 REPLIES 3
v-janeyg-msft
Community Support
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)

vjaneygmsft_0-1626404979987.png

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:

 

AttemptOutcome AOutcome BOutcome COutcome DOutcome EGrand Total Measure AMeasure BMeasure CMeasure DMeasure E
114236147575335678909044294 -74.06%387.28%-522.94%25.82%34.54%
212644114932523456874436589 -29.24%306.33%-1047.52%31.40%34.03%
311865910011923765428761 34.27%232.20%677.57%36.26%27.11%
411299796273123654326000 48.01%214.05%445.88%33.63%25.92%
5111526682695677432127901 -29.53%259.41%-878.44%18.32%36.08%
618796380379073219524 -1696.26%101.24%-5.97%3.49%13.28%
7174955452977217421 -18580.95%81.41%-0.44%0.28%1.71%
814794261245415819 -286000.00%64.00%-0.02%0.02%1.36%
91203243913213658 -125100.00%40.23%-0.03%0.03%0.44%
10149443111595 -29600.00%6.53%-0.02%0.17%0.50%
TOTAL676556906211501599836697190562      

 

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.

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)

vjaneygmsft_0-1626676909521.png

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

 

Best Regards

Janey Guo

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.